CODEWITHSUNDEEP
sqlpostgresqljsonb
Bryant Makes Programs
Bryant Makes Programs

Reputation: 1694

Querying Deep JSONb Information - PostgreSQL

I have the following JSON array stored on a row:

{
    "openings": [
         {
             "visibleFormData": {
                 "productName": "test"
             }
         }
    ]
}

I'm trying to get the value of productName. So far I've tried something like this:

SELECT tbl.column->'openings'->'0'->'visibleFormData'->>'productName'

The theory being that this would grab the first object (index 0) in the openings array and then grab the productName attribute from that object's visibleFormData object.

All I'm getting is null, though. I've tried multiple configurations of this. I'm thinking it has to do with the grabbing of index zero, but I am unsure. I am not a regular PSQL user, so it's proving a tad tricky to debug.

Upvotes: 0

Views: 29

Answers (1)

klin
klin

Reputation: 121524

The json array index is integer, so use 0 instead of '0':

with tbl(col) as (
values
    ('{
        "openings": [
             {
                 "visibleFormData": {
                     "productName": "test"
                 }
             }
        ]
    }'::jsonb)
)

SELECT tbl.col->'openings'->0->'visibleFormData'->>'productName'
FROM tbl

 ?column? 
----------
 test
(1 row)

Upvotes: 1

Related Questions