sqlhana

In SQL how to validate dynamic key-value pairs against key-value columns


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.


Solution

  • 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)