Блог

В базе данных PostgreSQL у меня есть вызываемый столбец json json . Данные внутри выглядят так, как показано ниже:

 {
    "Version": "0.0.0.1",
    "Items": [
        {
            "Id": "40000000-0000-0000-0000-000000141146",
            "Name": "apple",
            "Score": 64,
            "Value": 1430000
        },
        {
            "Id": "40000000-0000-0000-0000-000000141147",
            "Name": "grapefruit",
            "Score": 58,
            "Value": 1190000
        },
        {
            "Id": "40000000-0000-0000-0000-000000141148",
            "Name": "mango",
            "Score": 41,
            "Value": 170000
        }
    ]
}
  

Что я хотел бы сделать, так это извлечь все Score данные из Items элементов.

Я пытался использовать код SQL:

 select
substring(json ->> 'Items' from '"Score": (d*),') as score
from vegetables;
  

Однако это возвращает только оценку из первого элемента вместо 3. Я пытался использовать флаг ‘ g’, который должен был находить все результаты глобально, но код не работал.

Кто-нибудь может посоветовать, как это сделать правильно? Заранее спасибо!

Учитывая, что тип данных json поля jsonb тогда не нужно использовать substring или regex, simply lateral join with jsonb_array_elements сделает все необходимое для вас. Попробуйте выполнить запрос ниже.

 select x->>'Score' "Score" from vegetables 
cross join lateral jsonb_array_elements(json->'Items') x
  

ДЕМОНСТРАЦИЯ