How to filter JSON array in Postgres

Question

I currently have this LEFT JOIN which is part of a bigger select

LEFT JOIN (
  SELECT
    tags_components.component_id,
    array_to_json(array_agg(tags.*)) as tags
    FROM tags_components
    LEFT JOIN tags ON tags.id = tags_components.tag_id AND tags_components.component_name = 'contact'
    GROUP BY tags_components.component_id
) AS tags ON tags.component_id = contact.id

Which works as expected if component has all tags assigned. However tags array is always of size COUNT(tags.*) so for component without any tags is filled with null. Is there a way how filter those nulls out? I tried different things like using json_strip_nulls or having FILTER on the array but I didn't achieve the right result (JSON array containing only non-nulls)

Kristo Mägi · Accepted Answer · 2017-06-25 15:07:46Z

4

If I understood everything correctly then the issue you face is in the line:

...
array_to_json(array_agg(tags.*)) as tags
...

Maybe you used FILTER in a wrong way, but this does work to eliminta NULL results like:

SELECT  array_to_json(
            -- FILTER is applied to this specific 'array_agg'
            array_agg( t.* ) FILTER ( WHERE t.tag IS NOT NULL )
        )
FROM    ( VALUES
            ( 'a1' ),
            ( 'b1' ),
            ( null ),
            ( 'c1' ),
            ( null ),
            ( 'd1' )
        ) t( tag );

-- Resolves to:
                     array_to_json
-------------------------------------------------------
 [{"tag":"a1"},{"tag":"b1"},{"tag":"c1"},{"tag":"d1"}]
(1 row)

Alternatively you can use jsonb_agg (read more at Postgres Aggregate Functions) instead of array_to_json + array_agg to provide same result like:

SELECT  jsonb_agg( t.* ) FILTER ( WHERE t.tag IS NOT NULL )
FROM    ( VALUES
            ( 'a1' ),
            ( 'b1' ),
            ( null ),
            ( 'c1' ),
            ( null ),
            ( 'd1' )
        ) t( tag );

edited Jun 25, 2017 at 15:07

answered Jun 25, 2017 at 14:57

Kristo Mägi

1,70413 silver badges16 bronze badges

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

1 Comment

Petr Mensik Over a year ago

Thank you, the filter really worked. I would bet I wrote exactly the same code but apparently not :)

Sammy Roberts · Accepted Answer · 2020-01-24 18:56:58Z

2

array_remove function is going to be your best bet now: array_to_json(array_remove(array_agg(tags.*), null)) as tags

answered Jan 24, 2020 at 18:56

Sammy Roberts

6571 gold badge7 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to filter JSON array in Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related