Retrieving data from JSON-Object using SQL (snowflake)

Admin 1
Viewed 76

I receive data from an api. The data looks like this:

'displayValues': {
'mobDeviceTypeId': {
    '0': 'Unknown',
    '2': 'Smart Phone',
    '4': 'Tablet',
    '-1': 'Name is not available (-1)',
    '-7': 'NA'
}

}

I´d like to store the key-value pairs for the "mobDeviceTypeId" in a table with two columns (id, device) using SQL. My problem, in comparison to other JSON objects, is that the keys differ. So, for example:

SELECT $1:"adSizeId":"10"
FROM <table>

won´t help me. As I use snowflake, I also tried it with the flatten function, but I did not get the expected result.

I would be very grateful for tips and possible solutions!

Thank you!

snowflake-cloud-data-platform
1 Answers
with j(v) as (
  select parse_json('
  {"displayValues": {
      "mobDeviceTypeId": {
          "0": "Unknown",
          "2": "Smart Phone",
          "4": "Tablet",
          "-1": "Name is not available (-1)",
          "-7": "NA"
      }
  }}')
)
select key::integer as id, value::varchar as device
from j, lateral flatten(input => v:displayValues:mobDeviceTypeId);
ID DEVICE
-1 Name is not available (-1)
-7 NA
0 Unknown
2 Smart Phone
4 Tablet
user_950140
Related