Postgesql and JsonB: How to query list nested in an object

Question

I've looked at example after example, and I cannot find the syntax for what I need to do. With the below jsonb data, can someone tell me how to query the lastUpdatedTimestamp?

{
    "name": "Company name",
    "professionalData": [{
            "unit": "unit 1",
            "id": "1228",
            "school": "University of California-Berkeley, Haas School of Business",
            "quarter": "Q1",
            "lastUpdatedTimestamp": "2020-10-27T18:05:00Z"
        },
        {
            "unit": "unit 2",
            "id": "1228",
            "school": "University of California-Berkeley, Haas School of Business",
            "quarter": "Q1",
            "lastUpdatedTimestamp": "2020-10-29T12:10:00Z"
        }
    ]
}

I can get the professionalData object, but how do I then get an element within the array nested in professionalData? This is my exact query:

SELECT 
    elements
FROM product_reference_master p,
    jsonb_array_elements(p.prd_ref_dta_mas_json->'professionalData') as elements

And I get an error: ERROR: cannot extract elements from an object

I'm using Postgresql Aurora in AWS. I'm unsure of the exact version...

Always specify the version you are using.

Christophe Roussy
– Christophe Roussy

2021-01-20 16:51:06 +00:00
Commented Jan 20, 2021 at 16:51 — Christophe Roussy
– Christophe Roussy, Commented Jan 20, 2021 at 16:51
Thanks, of course. I'll edit my question.

aatuc210
– aatuc210

2021-01-20 16:51:57 +00:00
Commented Jan 20, 2021 at 16:51 — aatuc210
– aatuc210, Commented Jan 20, 2021 at 16:51

S-Man · Accepted Answer · 2021-01-20 16:48:28Z

1

demos:db<>fiddle

Expand the array with json_array_elements() and then you can query the inserted fields:

SELECT 
    elements -> 'lastUpdatedTimestamp'
FROM product_reference_master p,
    json_array_elements(p.prd_ref_json::jsonb->'professionalData') as elements

From Postgres 12, you can use json_path_query() alternatively:

SELECT
    *
FROM product_reference_master p,
    jsonb_path_query(p.prd_ref_json, '$.**.lastUpdatedTimestamp') as elements

edited Jan 20, 2021 at 16:48

answered Jan 20, 2021 at 16:39

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

8 Comments

aatuc210 Over a year ago

I tried something like that, but I get this error: ERROR: cannot extract elements from a scalar. professionalData is an object, not an array. Instead, it contains an array.

S-Man Over a year ago

Then something else seems to be wrong: For me it works: dbfiddle.uk/… Please try to reproduce your setting in the fiddle and share the related link afterwards

S-Man Over a year ago

Updated the answer

aatuc210 Over a year ago

My fiddle works as well, but the example isn't applying to my database. I still get an error that professionalData is a scalar object. dbfiddle.uk/…

S-Man Over a year ago

Maybe not all your data is type JSON? stackoverflow.com/questions/50296102/… Meaning: Your are casting text into json. Maybe not every record contains the expected structure?

|

Collectives™ on Stack Overflow

Postgesql and JsonB: How to query list nested in an object

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related