I have 2 tables in Snowflake which contain a Json field, and I need to make a join based on this field. When this json has the same keys and values it means it equals. The problem is that I can't tell if both fields will be given in the same order of keys or not. How can I make sure I do this join on the same ordered json?
For example, lets say we have table_a
| name | json_data |
|---|---|
| name_1 | {"key1": "A", "key2": "B"} |
| name_2 | {"key1": "C", "key2": "D"} |
I have 2 tables in Snowflake which contain a Json field, and I need to make a join based on this field. When this json has the same keys and values it means it equals. The problem is that I can't tell if both fields will be given in the same order of keys or not. How can I make sure I do this join on the same ordered json?
For example, lets say we have table_a
| name | json_data |
|---|---|
| name_1 | {"key1": "A", "key2": "B"} |
| name_2 | {"key1": "C", "key2": "D"} |
and table_b
| type | json_data |
|---|---|
| type_1 | {"key2": "B", "key1": "A"} |
| type_2 | {"key2": "C", "key1": "D"} |
The expected result for table_a a join table_b b on a.json_data = b.json_data is:
| name | type |
|---|---|
| name_1 | type_1 |
2 Answers
Reset to default 1You can use TRY_PARSE_JSON or PARSE_JSON(in case the json format is correct) on both columns which converts the values to a VARIANT type
SELECT a.name, b.type
FROM table_a a
INNER JOIN table_b b
ON TRY_PARSE_JSON(a.json_data) = TRY_PARSE_JSON(b.json_data) ;
If the order is the only issue, and the columns contain the same keys, you can use a setup like this to get around the order.
SELECT name, type
FROM data_table
JOIN type_table
ON type_table.json_data:key1 = data_table.json_data:key1
AND type_table.json_data:key2 = data_table.json_data:key2;
If there is a chance that some columns may be missing keys, you can always use EQUAL_NULL to get around this because columns that both have NULL for a specific key would be considered equal.