CODEWITHSUNDEEP
arraysjsonpostgresqlpostgresql-9.5
Petr Mensik
Petr Mensik

Reputation: 27496

How to filter JSON array in Postgres

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)

Upvotes: 1

Views: 5550

Answers (2)

Sammy Roberts
Sammy Roberts

Reputation: 657

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

Upvotes: 2

Kristo Mägi
Kristo Mägi

Reputation: 1684

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 );

Upvotes: 4

Related Questions