How to retrieve multiple data from jsonb column in postgres?

Question

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!

Welcome to SO. you should add the desired output also.

Akhilesh Mishra
– Akhilesh Mishra

2020-11-13 17:00:31 +00:00
Commented Nov 13, 2020 at 17:00 — Akhilesh Mishra
– Akhilesh Mishra, Commented Nov 13, 2020 at 17:00

Akhilesh Mishra · Accepted Answer · 2020-11-15 14:38:49Z

1

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

edited Nov 15, 2020 at 14:38

answered Nov 13, 2020 at 16:55

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to retrieve multiple data from jsonb column in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related