Expanding JSON array to text values with NULL

Question

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

Abelisto · Accepted Answer · 2018-09-20 18:35:24Z

6

And without join/union:

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

answered Sep 20, 2018 at 18:35

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

Comments

klin · Accepted Answer · 2018-09-20 17:56:18Z

4

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.

edited Sep 20, 2018 at 17:56

answered Sep 20, 2018 at 17:23

klin

123k15 gold badges240 silver badges262 bronze badges

2 Comments

Shiva Over a year ago

Is ..on true psql specific or standard SQL? Can you explain how/why it works?

bendytree Over a year ago

This solution is nice because it works if categories is missing or if it's an empty array

miensol · Accepted Answer · 2018-09-20 17:24:04Z

0

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

answered Sep 20, 2018 at 17:24

miensol

41.9k9 gold badges127 silver badges116 bronze badges

Comments

Shiva · Accepted Answer · 2018-09-20 17:32:25Z

0

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

answered Sep 20, 2018 at 17:32

Shiva

2,86826 silver badges40 bronze badges

Collectives™ on Stack Overflow

Expanding JSON array to text values with NULL

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related