1

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
}
thatguy
  • 21,059
  • 6
  • 30
  • 40
Sundar
  • 95
  • 1
  • 13
  • 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 Aug 10 '20 at 16:16
  • do you expect a string null ? in your file ? – hopeIsTheonlyWeapon Aug 10 '20 at 16:25
  • @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 Aug 11 '20 at 12:49
  • 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 Aug 11 '20 at 12:56
  • @hopeIsTheonlyWeapon no. Actual null not "null" or 'null' . – Sundar Aug 11 '20 at 13:06
  • @MikeWalton when queried , es is not returning all attributes which doesn't have any values(with null ), any special query is needed.? – Sundar Aug 11 '20 at 13:15

4 Answers4

5

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')
David Anselmo Rangel
  • 66
  • 1
  • 2
0

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.

Rajib Deb
  • 1,496
  • 11
  • 30
  • First thanks for your reply. select object_construct('id',1,'alpha', ifnull(alpha ,parse_json('null')) from sometable); returns {} Parse JSon does'nt work inside ifnull. – Sundar Aug 11 '20 at 12:50
  • "take care of the retrieval from Elastic." _search on index returns not null fields attributes only. is there any way to return not indexed attribute with null? – Sundar Aug 11 '20 at 14:15
-1

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;
hopeIsTheonlyWeapon
  • 557
  • 6
  • 19
-1
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'

Paresh Soneji
  • 13
  • 1
  • 6