jsonb snippets

Postgres SQL query for extracting and querying a JSON object containing an array of JSON objects

Tagged json, jsonb, postgres  Languages json, sql

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": "010170-XXXX"
      }
   ]
 }
}

In Postgres we can do this with 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';