Creating an array from multiple columns without NULL elements

Question

I'm trying to build a query to aggregate together multiple columns in a legacy table stored in a similar structure as below:

CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
  (1,'Router','Networking','Sale',NULL),
  (2,NULL,'Router','Networking','Sale'),
  (3,NULL,NULL,'Networking','Sale'),
  (4,NULL,NULL,NULL,NULL)
) AS t(id,tag_1,tag_2,tag_3,tag_4);

An example of NOT WHAT I WANT

This is an example of the query I want to build:

SELECT ID, json_build_array(Tag_1, Tag_2, Tag_3, Tag_4) AS tags
FROM table

The problem is that the above query adds the NULL values from the rows to the array:

ID  Tags
--------------------------------------------------
1   ['Router', 'Networking', 'Sale', null]
2   [null, 'Router', 'Networking', 'Sale']
3   [null, null, 'Networking', 'Sale']
4   [null, null, null, null]

I want to avoid having to write an overly complicated CASE WHEN statement to filter out the NULLs and I'm still new to working PostgreSQL's JSON datatypes. Is there anyway I can avoid including NULLs when building a JSON array in Postgres?

You don't say what you want in the case of id=4. To be pruned. You should always show what you want and not what you're currently generating. We tend to skim over this stuff and skip English. Also, study that CREATE TABLE AS and try to use that syntax to demonstrate your data in the future. It helps us get up with the DDL and DML needed. — Evan Carroll
– Evan Carroll, Commented Jan 17, 2017 at 18:13

Evan Carroll · Accepted Answer · 2017-01-17 18:11:36Z

I would suggest not using a JSON array, and instead using the native SQL array syntax which is likely much faster and more efficiently stored. It's also stronger typed. The JSON array is "possibly-heterogeneously-typed" per the docs.

I also wouldn't do this routinely. I would alter the table's schema to have an ARRAY (preferably SQL) on the table itself to store the tags without ever storing null in columns. This can put you down the path of correcting the schema.

Building arrays

Strictly-typed PostgreSQL array

Just use the ARRAY literal constructor.

 SELECT id, ARRAY[tag_1,tag_2,tag_3,tag_4] FROM foo;
 id |             array             
----+-------------------------------
  1 | {Router,Networking,Sale,NULL}
  2 | {NULL,Router,Networking,Sale}
  3 | {NULL,NULL,Networking,Sale}
  4 | {NULL,NULL,NULL,NULL}

A JSON array

SELECT id, json_build_array(tag_1,tag_2,tag_3,tag_4) FROM foo;
 id |            json_build_array            
----+----------------------------------------
  1 | ["Router", "Networking", "Sale", null]
  2 | [null, "Router", "Networking", "Sale"]
  3 | [null, null, "Networking", "Sale"]
  4 | [null, null, null, null]
(4 rows)

Filtering Nulls without manual `coalesce`

Strictly-typed PostgreSQL array

You can easily filter nulls in a single pass by wrapping the above in array_remove.

SELECT id, array_remove(ARRAY[tag_1,tag_2,tag_3,tag_4], null)
FROM foo;

 id |       array_remove       
----+--------------------------
  1 | {Router,Networking,Sale}
  2 | {Router,Networking,Sale}
  3 | {Networking,Sale}
  4 | {}

JSON array

SELECT id,jsonb_agg(elem)
FROM (SELECT id, ARRAY[tag_1,tag_2,tag_3,tag_4] FROM foo) AS g
CROSS JOIN LATERAL unnest(g.array)
  WITH ORDINALITY AS t(elem,ord)
WHERE elem IS NOT NULL
GROUP BY id
ORDER BY id;

 id |            jsonb_agg             
----+----------------------------------
  1 | ["Router", "Networking", "Sale"]
  2 | ["Router", "Networking", "Sale"]
  3 | ["Networking", "Sale"]

Thanks, I went with the PostgreSQL array solution and wrapped the query to remove the empty array. This was my resulting solution SELECT a.id, CASE WHEN a.tags = '{}' THEN NULL ELSE array_to_json(a.tags) END FROM (SELECT id, array_remove(ARRAY[tag_1,tag_2,tag_3,tag_4], null) AS tags FROM foo) a — Sathariel
– Sathariel, Commented Jan 17, 2017 at 19:50

ypercubeᵀᴹ · Accepted Answer · 2017-01-17 17:54:46Z

3

This should work:

SELECT 
    id, 
    ( SELECT json_agg(tag ORDER BY no) 
      FROM 
        ( SELECT 1, tag_1 UNION ALL 
          SELECT 2, tag_2 UNION ALL 
          SELECT 3, tag_3 UNION ALL
          SELECT 4, tag_4
        )  AS x (no, tag)
      WHERE tag IS NOT NULL
    ) AS tags 
FROM t  ;

answered Jan 17, 2017 at 17:54

ypercubeᵀᴹ

100k13 gold badges217 silver badges306 bronze badges

Add a comment |

Stack Exchange Network

Creating an array from multiple columns without NULL elements

An example of NOT WHAT I WANT

2 Answers 2

Building arrays

Strictly-typed PostgreSQL array

A JSON array

Filtering Nulls without manual `coalesce`

Strictly-typed PostgreSQL array

JSON array

Your Answer

Hot Network Questions

Creating an array from multiple columns without NULL elements

An example of NOT WHAT I WANT

2 Answers 2

Building arrays

Strictly-typed PostgreSQL array

A JSON array

Filtering Nulls without manual coalesce

Strictly-typed PostgreSQL array

JSON array

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions

Filtering Nulls without manual `coalesce`