Expanding JSON array to text values with NULL

user_1367532 0
Viewed 930

I'm having issues to generate a query to display a JSON array to a set of text values when having NULL values. Suppose I have the following table:

Name | Meta
Art0 | {"category":["sport"]}
Art1 | [NULL]
Art2 | {"category":["sport", "health"]}

If I do something like:

SELECT name, jsonb_array_elements_text(meta->'category') tag FROM table

I'm getting the following results:

Name | Tag
Art0 | sport
Art2 | sport
Art2 | health

The problem is that Art1 is being removed. How can I execute a query that also includes a row for Art1 and an empty string or NULL value on the Tag column?

Thank you

postgresql json
4 Answers

And without join/union:

SELECT
  name,
  jsonb_array_elements_text(coalesce(meta->'category', '[null]')) tag
FROM table;
user_5931440

Use left join:

select name, tag 
from my_table
left join jsonb_array_elements_text(meta->'category') as tag on true

 name |  tag   
------+--------
 Art0 | sport
 Art1 | 
 Art2 | sport
 Art2 | health
(4 rows)

This is a lateral join, i.e. the function is executed once for each row from the table. Usually such a join is written down as cross join but we want to get results also when the function returns nothing. To accomplish this we can use left join, which needs a join condition, so we are using true as always fulfilled condition.

user_19957380

You can do so with LEFT JOIN just like when dealing with tables:

SELECT name, tag FROM table
LEFT OUTER JOIN jsonb_array_elements_text(meta->'category') tag ON TRUE

Would result in:

name    tag
Art0    sport
Art1    (null)
Art2    sport
Art2    health
user_1552130

Using union

SELECT name, jsonb_array_elements_text(meta->'category') tag FROM table UNION 
SELECT id, attrs::text tag FROM table where meta is null;

Result:

name    tag
Art0    sport
Art2    sport
Art2    health
Art1
user_30074020
Related