jsonb find a value in an array

user_12619318 0
Viewed 52

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}]'
jsonb postgresql arrays sql json
1 Answers

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.

user_3303150
Related