I have a SQL table that contains key - value pairs, looks like this:
Key | Value |
---|---|
product_id | iphone_14 |
product_id | iphone_14_pro |
country_code | USA |
country_code | CA |
Those rows identify allowed key-value pairs, meaning if a combination of key and value is not in the table - than it is invalid.
In the API request I would receive dynamic key - value pairs and I need to identify which of them are invalid.
For example, when I get these data:
[
{
"key": "product_id",
"value": "iphone_14"
},
{
"key": "product_id",
"value": "iphone_14_pro"
},
{
"key": "country_code",
"value": "INVALID_VALUE"
},
{
"key": "country_code",
"value": "USA"
}
]
we can say that item #3 in array is invalid, since INVALID_VALUE
is not in the column of allowed values for country_code
.
How would I write a SQL query that would identify invalid key-value pairs passed. Like in this case, I would have to identify that invalid key
is country_code
and invalid value
is INVALID_VALUE
(#3 item in the array).
Please note: all of the data is dynamic.
UPDATE: I am using SAP HANA dbms, tough I understand there are not so many people using it. So, maybe the answer can provide some general guidelines on how to do it.
Without knowing more details, it is hard to determine what the most efficient solution in your context would be. But if you are looking for one statement, which does the job based on your JSON input, you could consider using function JSON_TABLE:
SELECT *
FROM JSON_TABLE('[
{"key": "product_id","value": "iphone_14"},
{"key": "product_id","value": "iphone_14_pro"},
{"key": "country_code","value": "INVALID_VALUE"},
{"key": "country_code","value": "USA"}
]', '$'
COLUMNS
(
KEY nvarchar(20) PATH '$.key',
VALUE nvarchar(20) PATH '$.value'
)
) WHERE (KEY, VALUE) NOT IN (SELECT KEY, VALUE FROM VALIDATION)