Querying an array within an array with Postgres JSONB query

Question

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?

klin · Accepted Answer · 2017-08-16 07:13:46Z

2

Use the function jsonb_array_elements():

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

answered Aug 16, 2017 at 7:13

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

see sharper Over a year ago

I was excited, but: "cannot extract elements from an object".

klin Over a year ago

This means that the actual json structure is different than that described in your question. The name object must be an array in every row. You can test it here.

see sharper Over a year ago

Ah! I forgot to limit the query by resourceType. Thanks - it works.

Collectives™ on Stack Overflow

Querying an array within an array with Postgres JSONB query

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