score:2

Accepted answer

For Postgres you have two choices to make this query work properly:

select *
from the_table
where jsonb_array_length(json::jsonb -> 'thirdTypeLabels') = 0;

Or - starting with Postgres 12 - using a JSON Path expression

select *
from the_table
where jsonb_path_exists(json::jsonb, '$.thirdTypeLabels.size() ? (@ == 0)' );

Or use the same JSON path expression as in Oracle:

select *
from the_table
where jsonb_path_exists(json::jsonb, '$' ? (@.thirdTypeLabels.size() == 0)');

In Postgres you should also use a column defined as jsonb rather than text (or varchar)

score:3

You should use a proper JSON parser otherwise there is no guarantee that %thirdTypeLabels%[]% will restrict the match of the empty array to the thirdTypeLabels key-value pair.

So for Oracle 18c you can use:

SELECT id,
       thirdTypeLabelsCount
FROM   mytable t
       CROSS JOIN
       JSON_TABLE(
         t.json,
         '$'
         COLUMNS(
           thirdTypeLabelsCount NUMBER PATH '$.thirdTypeLabels.size()'
         )
       )
WHERE  thirdTypeLabelsCount = 0;

or

SELECT *
FROM   mytable
WHERE  JSON_EXISTS( json, '$ ? (@.thirdTypeLabels.size() == 0) ' )

db<>fiddle


More questions

More questions with similar tag