PostgreSQL-10: query JSONB property with multiple types

Question

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?

GMB · Accepted Answer · 2020-03-08 09:15:26Z

1

How about casting to text instead?

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

answered Mar 8, 2020 at 9:15

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Georgios Varisteas Over a year ago

Yes, of course this works! So clumsy of me. This is actually the first thing I tried before posting the question. It failed for reasons irrelevant to this problem that I missed.

Collectives™ on Stack Overflow

PostgreSQL-10: query JSONB property with multiple types

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related