Querying json field by array index and object property

Question

I have a column like:

[
   {
      "type":"menu",
      "prompt": {
            "type":"say",
            "message":"This is a menu. Pick option 1 or 2."
      }
   }
]

The first tier object could contain a variety of types and any number of types. My objective (for automated testing, not production, so it can be inefficient) is to find a record with any column.*.type = menu.

Based on https://stackoverflow.com/a/22687653/197606 I understand I can provide a numeric index, but running the query SELECT * FROM my_table WHERE 'my_column'->0->'type' = 'menu' throws the error:

ERROR:  operator is not unique: unknown -> integer
LINE 1: SELECT * FROM my_table WHERE 'my_column'->0->'type' =...
                                                       ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
Query failed
PostgreSQL said: operator is not unique: unknown -> integer
Hint: Could not choose a best candidate operator. You might need to add explicit type casts.

While specifying a numeric index of 0 would work for this specific use case and is an acceptable answer, I'd prefer an approach that isn't picky about the column index.

Do you need this: SELECT * FROM my_table WHERE my_column->0->>'type' = 'menu' ? — Oto Shavadze
– Oto Shavadze, Commented May 4, 2017 at 19:11
@OtoShavadze Can you post this as an answer so I can give you credit? — Ben
– Ben, Commented May 13, 2017 at 15:56

Oto Shavadze · Accepted Answer · 2017-05-13 16:50:38Z

1

You can use this:

SELECT * FROM my_table WHERE my_column->0->>'type' = 'menu'

answered May 13, 2017 at 16:50

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

Comments

Roman Tkachuk · Accepted Answer · 2017-05-04 19:59:55Z

0

PostgreSQL does not allow to look in json array. Your solution might be something like this:

SELECT *
FROM my_table
WHERE EXISTS (
      SELECT 1
      FROM  jsonb_array_elements(my_column) AS j
      WHERE j->>'type' = 'menu'
)

answered May 4, 2017 at 19:59

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

Collectives™ on Stack Overflow

Querying json field by array index and object property

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related