Snowflake : Object_construct leaving null values when i used copy command to frame json file as out put

0
votes

I use copy command of snowflake which is below returns a file with content json

 copy into @elasticsearch/product/sf_index 
 from (select object_construct('id',id, alpha,'alpha')from table limit 1)  
 file_format = (type = json, COMPRESSION=NONE), overwrite=TRUE, single = TRUE, max_file_size=5368709120;

data is id alpha 1 null

the output file is

{
   "id" :1
}

but I need to have the null values

{
   "id" :  1,
   "alpha" : null
}
4
snowflake-cloud-data-platformsnowflake-schema
Out of curiosity, why do you need the null values in your JSON? That defeats the purpose of JSON, which is to send only the data that is needed. - Mike Walton
do you expect a string null ? in your file ? - hopeIsTheonlyWeapon
@MikeWalton I'm indexing the records in es, for the developer, they should have null values for handling es search result. that's the reason. Thanks - Sundar
When querying JSON data, you will get a NULL returned for any JSON attribute that isn't present in the data. So, there shouldn't be a need to include the attribute with a NULL. - Mike Walton
@hopeIsTheonlyWeapon no. Actual null not "null" or 'null' . - Sundar

4 Answers

2
votes

You can use the function OBJECT_CONSTRUCT_KEEP_NULL.

Documentation: https://docs.snowflake.com/en/sql-reference/functions/object_construct_keep_null.html

Example:

select OBJECT_CONSTRUCT_KEEP_NULL('id',id, alpha,'alpha')
0
votes

Will it be possible for you to check programmatically if the value is null and it is null use the below

select object_construct('id',1,'alpha',parse_json('null'));

Per SnowFlake documentation

If the key or value is NULL (i.e. SQL NULL), the key-value pair will be omitted from the resulting object. A key-value pair consisting of a not-null string as key and a JSON NULL as value (i.e. PARSE_JSON(‘NULL’)) will not be omitted.

The other option is, just send it without the null attribute in Elastic and then take care of the retrieval from Elastic.

-1
votes

How about this

select object_construct('id',id, 'alpha', case when alpha is not null  then alpha  else 'null' end )from table limit 1;

case should be supported by the copy command.

"null" is a valid in json document as per this SO

Is null valid JSON (4 bytes, nothing else)

Ok another possible way is this using union

select object_construct('id',id, 'alpha', parse_json('NULL') )from table  where  alpha is null
union 
select object_construct('id',id, 'alpha', alpha )from table  where  alpha is not null;
-1
votes
select object_construct('id', id,'alpha', IFNULL(alpha, PARSE_JSON('null'))) from table limit 1

Use IFNULL to check if the value is null and replace with JSON 'null'