Let's say I have a complex struct, perhaps with ten levels of nesting and repeated fields. Is there a built-in way to compare these two objects to see if they are the same minus the sorting of keys? This may be related to: Compare two json values for equality. An example might be:
{
"id": "0001",
"type": "donut",
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "type": "Powdered Sugar" , "id": "5007"},
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
],
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devil's Food" }
]
}
}
Versus:
{
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devil's Food" }
]
},
"topping":
[
{ "type": "None", "id": "5001" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5007", "type": "Powdered Sugar" },
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}
CodePudding user response:
From the previous question, we learnt that JSON type allows such a comparison, then it is just a matter of how to use JSON type as a proxy to compare the 2 structs
with data as (
select struct<a string, b struct<x string, y string>>('a', ('x', 'y')) col1,
struct<b struct<y string, x string>, a string>(('y', 'x'), 'a') col2,
) select col1,
col2,
TO_JSON_STRING(PARSE_JSON(TO_JSON_STRING(col1))) = TO_JSON_STRING(PARSE_JSON(TO_JSON_STRING(col2)))
from data;