Search code examples
postgresqljsonb

Querying data by empty json or with error data

I have a table in Postgresql that have a jsonb column. This column can have 3 types of data, in the following structure:

  1. [] if the result is empty;
  2. [{"items": [{"name": "item1", "value": "value1", "messages": []}]}]"
  3. [{"items": [{"name": "error", "value": "some error", "messages": []}]}]

If the first item is not an error, I have to show the data. And also I have an interface to get all the errors. I'm using a COUNT WHERE FILTER to get the counting of empty and errors and it is working.

This is the example query:

SELECT COUNT(*) FILTER (WHERE t1.my_column = '[]' OR items -> 'items' -> 0 ->> 'name' = 'error') --This works
FROM my_table t1
LEFT JOIN jsonb_array_elements(CAST(t1.my_column AS jsonb)) AS items ON TRUE

But when I try to use it on another query, it returns cannot extract elements from an object:

SELECT t1.id, t1.column1, ...
FROM my_table t1
LEFT JOIN jsonb_array_elements(CAST(t1.my_column AS jsonb)) AS items ON TRUE
WHERE t1.my_column <> '[]' --This works
OR items -> 'items' -> 0 ->> 'name' <> 'error' --This doesn't

It seems it cannot extract data from an empty array, but I actually don't know how to make it work for both cases.

Can someone help me?

Solution

  • Your second query references the alias for the table (a "record"), not the actual value that is returned by jsonb_array_elements.

    You need to change that to:

    SELECT * 
FROM my_table t1
  LEFT JOIN jsonb_array_elements(t1.my_column) with ordinality AS items(item) ON true
where item.item -> 'items' -> 0 ->> 'name' <> 'error'

    Note that the where condition on the outer joined table turns your outer join back into an inner join. It's effectively the same as 

    SELECT * 
FROM my_table t1
  JOIN jsonb_array_elements(t1.my_column) AS items(item) 
    ON items.item -> 'items' -> 0 ->> 'name' <> 'error'