Snowflake JSON with foreign language to tabular format dynamically

Question

I read through snowflake documentation and the web and found only one solution to my problem by https://stackoverflow.com/users/12756381/greg-pavlik which can be found here Snowflake JSON to tabular

This doesn't work on data with Russian attribute names and attribute values. What modifications can be made for this to fit my case? Here is an example:

    create or replace table target_json_table(
v variant
);

INSERT INTO target_json_table SELECT parse_json('{
  "at": {
    "cf": "NV"  
  },
  "pd": {
    "мо": "мо",
    "ä": "ä",
   "retailerName": "retailer",
    "productName":"product"
  }
}');

  call create_view_over_json('target_json_table', 'V', 'MY_VIEW');

ERROR: Encountered an error while creating the view. SQL compilation error: syntax error line 7 at position 7 unexpected 'ä:'. syntax error line 8 at position 7 unexpected 'мо'.

Please update the question providing sample column names and data that can reproduce the problem with the schema inference stored procedure you reference in that post. — Greg Pavlik, May 13 '22 at 15:21

score 1 · Accepted Answer · answered May 16 '22 at 15:55

There was a bug in the original SQL used as a basis for the creation of the stored procedure. I have corrected that. You can get an update on the Github page. The changed section is here:

sql = 
`
SELECT DISTINCT '"' || array_to_string(split(f.path, '.'), '"."') || '"'                                         AS path_nAme,       -- This generates paths with levels enclosed by double quotes (ex: "path"."to"."element").  It also strips any bracket-enclosed array element references (like "[0]")
                DECODE (substr(typeof(f.value),1,1),'A','ARRAY','B','BOOLEAN','I','FLOAT','D','FLOAT','STRING')  AS attribute_type,  -- This generates column datatypes of ARRAY, BOOLEAN, FLOAT, and STRING only
                '"' || array_to_string(split(f.path, '.'), '.') || '"'                                           AS alias_name       -- This generates column aliases based on the path
FROM
        @~TABLE_NAME~@,
        LATERAL FLATTEN(@~COL_NAME~@, RECURSIVE=>true) f
WHERE   TYPEOF(f.value) != 'OBJECT'
        AND NOT contains(f.path, '[')         -- This prevents traversal down into arrays
limit   ${ROW_SAMPLE_SIZE}
`;

Previously this SQL simply replaced non-ASCII characters with underscores. The updated SQL will wrap key names in double quotes to create non-ASCII key names.

Be sure that's what you want it to do. Also, the keys are nested. I decided that the best way to handle that is to create column names in the view with dot notation, for example one column name is pd.ä. That will require wrapping the column name with double quotes, such as:

select * from MY_VIEW where "pd.ä" = 'ä';

Final note: The name of your stored procedure is create_view_over_json, however, in the Github project the name is create_view_over_variant. When you update, be sure to call the right procedure.

Snowflake JSON with foreign language to tabular format dynamically

1 Answers1