CODEWITHSUNDEEP
arraysjsonpostgresqljsonb
see sharper
see sharper

Reputation: 12055

Querying an array within an array with Postgres JSONB query

I have some JSON in a field in my Postgres 9.4 db and I want to find rows where the given name is a certain value, where the field is named model and the JSON structure is as follows:

{
    "resourceType": "Person",
    "id": "8a7b72b1-49ec-43e5-bd21-bc62674d9875",
    "name": [
      {
        "family": [
          "NEWMAN"
        ],
        "given": [
          "JOHN"
        ]
      }
    ]
}

So I tried this: SELECT * FROM current WHERE model->'name' @> '{"given":["JOHN"]}'; (as well as various other guesses) but that does not match the above data. How should I do this?

Upvotes: 0

Views: 117

Answers (1)

klin
klin

Reputation: 121864

Use the function jsonb_array_elements():

select t.*
from current t,
jsonb_array_elements(model->'name') names
where names->'given' ? 'JOHN'

Upvotes: 2

Related Questions