Handle Null in jsonb_array_elements

Question

I have 2 tables a and b

Table a

id          |   name        | code
VARCHAR         VARCHAR       jsonb
1               xyz          [14, 15, 16 ]
2               abc          [null]
3               def          [null]

Table b

id | name | code
1    xyz    [16, 15, 14 ]
2    abc        [null]

I want to figure out where the code does not match for same id and name. I sort code column in b b/c i know it same but sorted differently

SELECT                  a.id,
                        a.name,
                        a.code,
                        c.id,
                        c.name,
                        c.code
                        
                        FROM                a
                        FULL OUTER JOIN     (   SELECT          id,
                                                                name,
                                                                jsonb_agg(code ORDER BY code) AS code
                                                FROM        (
                                                                SELECT          id,
                                                                                name,
                                                                                jsonb_array_elements(code) AS code
                                                                FROM            b
                                                                GROUP BY        id,
                                                                                name,
                                                                                jsonb_array_elements(code)
                                                            ) t
                                                GROUP BY        id,
                                                                name
                                            ) c
                            ON              a.id = c.id
                            AND             a.name = c.name
                            AND             COALESCE (a.code, '[]'::jsonb) = COALESCE (c.code, '[]'::jsonb)
                        WHERE               (a.id IS NULL OR c.id IS NULL)

My answer in this case should only return id = 3 b/c its not in b table but my query is returning id = 2 as well b/c i am not handling the null case well enough in the inner subquery How can i handle the null use case in the inner subquery?

Answer 1

demo:db<>fiddle

The <@ operator checks if all elements of the left array occur in the right one. The @> does other way round. So using both you can ensure that both arrays contain the same elements:

a.code @> b.code AND a.code <@ b.code

Nevertheless it will be accept as well if one array contains duplicates. So [42,42] will be the same as [42]. If you want to avoid this as well you should check the array length as well

AND jsonb_array_length(a.code) = jsonb_array_length(b.code)

Furthermore you might check if both values are NULL. This case has to be checked separately:

a.code IS NULL and b.code IS NULL

A little bit shorter form is using the COALESCE function:

COALESCE(a.code, b.code) IS NULL

So the whole query could look like this:

SELECT 
    *
FROM a
FULL OUTER JOIN b 
ON a.id = b.id AND a.name = b.name
AND (
    COALESCE(a.code, b.code) IS NULL            -- both null
    OR (a.code @> b.code AND a.code <@ b.code 
        AND jsonb_array_length(a.code) = jsonb_array_length(b.code) -- avoid accepting duplicates
    )
)

After that you are able to filter the NULL values in the WHERE clause