Postgresql get keys from nested array of objects in JSONB field

Question

Here' a dummy data for the jsonb column

{
"address": [
{
  "country": "US",
  "extension": [
    {
      "extension": [
        {
          "valueDecimal": -71.024638,
          "url": "latitude"
        },
        {
          "url": "longitude",
          "valueDecimal": 42.082543
        }
      ],
      "url": "url1"
    }
  ],
  "postalCode": "02301",
  "city": "Brockton"
},
{
  "country": "US",
  "extension": [
    {
      "extension": [
        {
          "valueDecimal": -71.024638,
          "url": "latitude"
        },
        {
          "url": "longitude",
          "valueDecimal": 42.082543
        }
      ],
      "url": "url2"
    }
  ],
  "postalCode": "02301",
  "city": "Brockton"
}
]
}

I want to get something like, address->'extension'->'extension'->'valueDecimal' but it only works with the json but it is an array of JSON data...my expecting output -

[-71.024638, -71.024638]

I'm able to get a first extension(i.e first array of JSON ) with the below query

SELECT elems.value  FROM "patient", jsonb_array_elements(resource -> 'extension') AS elems;

so the problem is that I'm not able to get inner 'extension'(It is inside the extension object) data. Any help will be Helpful.

Kaushik Nayak · Accepted Answer · 2019-05-08 14:42:47Z

2

You may cross join multiple levels of jsonb_array_elements

SELECT json_agg(elems3->>'valueDecimal') as latitudes
    FROM patient
 cross join jsonb_array_elements(resource ->'address'  ) AS elems
 cross join jsonb_array_elements(elems ->'extension')  AS elems2
 cross join jsonb_array_elements(elems2 ->'extension') AS elems3
 where elems3->>'url' = 'latitude'

DEMO

This may be slower for large records although there are ways to improve performance. A recommended option is to redesign and normalize your table to store values separately as columns in their corresponding tables and use JSON only where there's no other way to deal with the data.

answered May 8, 2019 at 14:42

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

3 Comments

Jitendra Over a year ago

Is there any way to index this nested data?

Kaushik Nayak Over a year ago

@Jitendra : Yes, as I said, there are options, for e.g using GIN index. You may explore them.

Jitendra Over a year ago

Yeah I tried that and it is working for nested json object but it is not working for multilevel array of json object. Something like this - CREATE INDEX idxginp ON patient USING gin ((resource->'maritalStatus'->'coding') jsonb_path_ops);

Collectives™ on Stack Overflow

Postgresql get keys from nested array of objects in JSONB field

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related