Snowflake ifnull and parse_json when combined it is not working as expected

Question

This is my query

select object_construct('id', id, alpha, PARSE_JSON(null)) from tablename limit 1

the output is { "id" :1, "alpha":null }

but when I combined parse_json with ifnull it returns empty object {} not working as expected

select object_construct('id',IFNULL(id, (PARSE_JSON(null)), alpha, IFNULL(alpha, (PARSE_JSON(null))) from tablename limit 1

the above returns {}

but I need to have the null values { "id" : 1, "alpha" : null }

combining is not working any solutions?

[Question Updated]

Thanks Felipe Hoffa.

In Your answer, the last part is working as expected.

SELECT OBJECT_CONSTRUCT('id', id, 'alpha', IFNULL(alpha, PARSE_JSON('null')))
FROM (SELECT 1 id, null alpha)
# {"alpha": null, "id": 1}

But When I try with the below, it is not Working

SELECT OBJECT_CONSTRUCT(
'id', id, 
'alpha', IFNULL(alpha, PARSE_JSON('null'))
 ) 
FROM (SELECT id, alpha from tableName)
#{ "id" :1 }

it works, when using your FROM query : FROM (SELECT 1 id, null alpha)

not working, when using my FROM query : FROM (SELECT id, alpha from tableName)

Hope cleared.

Thanks in Advance

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2020-09-10T04:01:36

The problem here is that the first statement of the question is not true. When encoding PARSE_JSON(null), null is not represented:

SELECT OBJECT_CONSTRUCT('id', id, alpha, PARSE_JSON(null))
FROM (SELECT 1 id, null alpha)

# {"id": 1}

To fix this, you have to do PARSE_JSON('null') instead -- and quote the name of the variable:

SELECT OBJECT_CONSTRUCT('id', id, 'alpha', PARSE_JSON('null'))
FROM (SELECT 1 id, null alpha)

# {"alpha": null, "id": 1}

That looks better. With that solved, now IF_NULL will work as desired:

SELECT OBJECT_CONSTRUCT('id', id, 'alpha', IFNULL(alpha, PARSE_JSON('null')))
FROM (SELECT 1 id, null alpha)

# {"alpha": null, "id": 1}