Postgres SQL query for extracting and querying a JSON object containing an array of JSON objects
We want to extract data by querying a JSON object (hash, dictionary, map, object) which contains an array of JSON objects:
{
"responses": [
{
"patient": {
"ssid": "101010-XXXX",
},
"patient": {
"ssid": "070710-XXXX",
}
]
}
}
In Postgresql 9.4 and higher we can write the following query to query nested arrays of objects:
SELECT
*
FROM
messages
WHERE
body -> 'responses' @> '[{"patient":[{"ssid":"070710-XXXX"}]}]';
In earlier versions of Postgres we can use the jsonb_array_elements function:
WITH json_messages AS (
SELECT jsonb_array_elements(body#>'{responses}')->'patient'->>'ssid', id from messages
)
SELECT * FROM json_messages WHERE ssid = '010150-XXXX';