Literal Flatten NULL object or Array

Question

I am trying to flatten an JSON object contained in a column of my table, which has an optional field.

-- row 1
{
    "key1": "value1",
    "key2": {
        "key3": [1, 2, 3]
    }
    
}
-- row 2
{
    "key1": "value1x"
}

From the above example lets assume the table is from table "X" and column "A" My query to select:

SELECT
   x.id, -- another column in table X
   key3
FROM X AS x,
LATERAL FLATTEN(A.key2.key3) AS key3

The resulting table:

id  |  key3
___________
 1  |   1
 1  |   2
 1  |   3

What I am trying to achieve:

id  |  key3
___________
 1  |   1
 1  |   2
 1  |   3
 2  |   NULL

Any ideas how to do this? One idea is to UNION for all the NULL object is there a better way?

Answer 1

To get non-existing values OUTER option could be applie:

OUTER

If TRUE, exactly one row is generated for zero-row expansions (with NULL in the KEY, INDEX, and VALUE columns).

SELECT
   x.id, -- another column in table X
   key3
FROM X AS x,
LATERAL FLATTEN(INPUTY => A.key2.key3,OUTER => TRUE) AS key3