2

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
  • 216,147
  • 25
  • 84
  • 135
Georgios Varisteas
  • 35
  • 1
  • 3

1 Answers1

1

How about casting to text instead?

WHERE (dt#>>'{b,d}')::text IN ('2', '4', '6', '9')
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 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. – Georgios Varisteas Mar 08 '20 at 09:59