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.
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'
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments