PostgreSQL-10: query JSONB property with multiple …

Assume a table json_table with a column data (jsonb). A sample value would be

{"a": [{"b":{"c": "xxx", "d": 1}},{"b":{"c": "xxx", "d": 2}}]}

I used to run SQL queries like the following:

SELECT data
FROM json_table j
WHERE NOT EXISTS (SELECT 1
                  FROM jsonb_array_elements(j.data#>'{a}') dt 
                  WHERE (dt#>>'{b,d}')::integer IN (2, 4, 6, 9)
                 );

The problem is that now property d needs to have a dual type, either integer or string. This means that the aforementioned query will crash with

ERROR: invalid input syntax for integer: "d-string-value"

I would like to avoid the obvious solution of creating two properties d_id and d_name.

So, is there any way to query the dual type JSONB property?

Answer

How about casting to text instead?

WHERE (dt#>>'{b,d}')::text IN ('2', '4', '6', '9')

PostgreSQL-10: query JSONB property with multiple types

Advertisement

Answer