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, May 04 '17 at 19:11
@OtoShavadze Can you post this as an answer so I can give you credit? — Ben, May 13 '17 at 15:56

score 1 · Accepted Answer · answered May 13 '17 at 16:50

1

You can use this:

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

answered May 13 '17 at 16:50

Oto Shavadze

40,603
55
152
236

score 0 · Answer 2 · answered May 04 '17 at 19:59

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 04 '17 at 19:59

Roman Tkachuk

3,096
1
16
15

Querying json field by array index and object property

2 Answers2