CODEWITHSUNDEEP
sqlarraysjsonpostgresqljsonb
hulxfp
hulxfp

Reputation: 53

jsonb find a value in an array

There is such a data structure:

Column "recipient" type jsonb

{
  "phoneNumbers": [
    {
      "isDefault": true,
      "type": "MOBILE",
      "number": "3454654645"
    },
    {
      "isDefault": true,
      "type": "MOBILE",
      "number": "12423543645"
    }
  ]
}

I need to write a search request by number. In the postgres documentation, I did not find a search by value in an array, only it is obtained by an index. It doesn't suit me

I made a query like this, it gets executed, but are there any other ways to search through an array?

SELECT * 
FROM my_table
WHERE recipient -> 'phoneNumbers' @> '[{"number":3454654645}]'

Upvotes: 2

Views: 92

Answers (1)

user330315
user330315

Reputation:

That's pretty much the best way, yes.

If you have a (GIST) index on recipient the index would not be used by your condition. But the following could make use of such an index:

SELECT * 
FROM my_table
WHERE recipient @> '["phoneNumbers": {"number":3454654645}]}'

If you are using Postgres 12 or later, you can also use a JSON path expression:

SELECT * 
FROM my_table
WHERE recipient @@ '$.phoneNumbers[*].number == "12423543645"'

If you can't pass a JSON object to your query, you can use an EXISTS sub-select:

SELECT mt.*
FROM my_table mt
WHERE EXISTS (SELECT * 
              FROM jsonb_array_elements_text(mt.recipient -> 'phoneNumbers') as x(element)
              WHERE x.element ->> 'number' = '3454654645')

The '3454654645' can be passed as a parameter to your query. This will never make use of an index though.

Upvotes: 3

Related Questions