CODEWITHSUNDEEP
sqlhana
mykhailoklym94
mykhailoklym94

Reputation: 588

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.

Upvotes: 0

Views: 149

Answers (3)

xQbert
xQbert

Reputation: 35323

Here's an executable example:

With SrcTable as (SELECT 'product_id' as key, 'iphone_14' as value from dummy union all
SELECT 'product_id' as key, 'iphone_14_pro' as value from dummy union all
SELECT 'country_code' as key, 'USA' as value from dummy union all
SELECT 'country_code' as key, 'CA' as value from dummy)
 
 
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'
        ))
EXCEPT --AKA MINUS
SELECT key, value FROM srcTable

To make work for you:

  • Eliminate the srctable with statement and replace the srcTable with your validation table name following the "EXCEPT"

  • You'll also need to pass in the string in the JSON_TABLE as a input parameter with a string with the assumption it has the key/value format as an input parameter with the limits defined in COLUMNS of the JSON_TABLE table value function. However, if the JSON values are stored somehwere already in the hana db; then they could be just selected. it is unclear how this value is to be made availale to the query.

Set based operators typically perform efficiently.

Upvotes: 1

Mathias Kemeter
Mathias Kemeter

Reputation: 1183

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)

Upvotes: 3

M. Oberauer
M. Oberauer

Reputation: 336

A simple yet not very performant solution would be to loop over the key-value pairs you receive via the API and then query if the provided pair is in the table with the valid combinations. Like this (pseudocode)

for k, v in kvpairsreceived do
  rowsfound = query("SELECT COUNT(0) FROM tablewithvalidcombos WHERE Key = k AND Value = v")
  if rowsfound < 1
    reportInvalidPair(k,v)

Upvotes: 0

Related Questions