How to retrieve multiple data from jsonb column in postgres?

user_14633658 0
Viewed 42

In PostgreSQL database I have a json column called json. Data inside look like below:

{
    "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
        }
    ]
}

What I would like to do is retrieving all Score data from Items elements.

I was trying to use SQL code:

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

However that returns just the score from first element instead of 3. I was trying to use '\g' flag which supposed to find all results globally, but the code was not working.

Could anyone advise how to do that properly? Thanks in advance!

postgresql json regex
1 Answers

Considering that the data type of json field is jsonb then no need to use substring or regex, simply lateral join with jsonb_array_elements will do the required things for you. Try below query.

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

DEMO

user_77724830
Related