Retrieving data from JSON-Object using SQL (snowflake)

Question

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!

Dave Welden · Accepted Answer · 2022-07-29 18:38:39Z

1

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

answered Jul 29, 2022 at 18:38

Dave Welden

1,9791 gold badge10 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Retrieving data from JSON-Object using SQL (snowflake)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related