CODEWITHSUNDEEP
amazon-redshift
Viktor Andriichuk
Viktor Andriichuk

Reputation: 51

RedShift: some troubles with nested json

I have next JSON:

{"promptnum":4,"corpuscode":"B0014","prompttype":"video","skipped":false,"transcription":"1","deviceinfo":{"DEVICE_ID":"exynos980","DEVICE_MANUFACTURER":"samsung","DEVICE_SERIAL":"unknown","DEVICE_DESIGN":"a51x","DEVICE_MODEL":"SM-A5160","DEVICE_OS":"android","DEVICE_OS_VERSION":"10","DEVICE_CARRIER":"","DEVICE_BATTERY_LEVEL":"70.00%","DEVICE_BATTERY_STATE":"unplugged","Current App Version":"1.1.0","Current App Build":"6"}}

I want to get values from 1-st level and 2-nd level.

1-st level: "promptnum":4,"corpuscode":"B0014","prompttype":"video","skipped":false,"transcription":"1","deviceinfo":...

2-nd level:

"deviceinfo":{"DEVICE_ID":"exynos980","DEVICE_MANUFACTURER":"samsung","DEVICE_SERIAL":"unknown","DEVICE_DESIGN":"a51x","DEVICE_MODEL":"SM-A5160","DEVICE_OS":"android","DEVICE_OS_VERSION":"10","DEVICE_CARRIER":"","DEVICE_BATTERY_LEVEL":"70.00%","DEVICE_BATTERY_STATE":"unplugged","Current App Version":"1.1.0","Current App Build":"6"}

When I parse 1-st level with

SELECT d.*
FROM (
  SELECT c.json_parse, c.json_parse.deviceinfo AS device_info
  FROM (
      SELECT JSON_PARSE(file_attr)
      FROM public.dc_ac_files
  ) AS c) AS d

it's work well.

But when I try to get values from 2-nd level with

SELECT d.*, l.DEVICE_ID
FROM (
  SELECT c.json_parse, c.json_parse.deviceinfo AS device_info
  FROM (
      SELECT JSON_PARSE(file_attr)
      FROM public.dc_ac_files
  ) AS c) AS d, d.device_info AS l

it doesn't work - no errors and no data.

If I know, it's right way to parse nested json, but it doesn't work for me.

Can you help me?

Upvotes: 0

Views: 1195

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

Viktor you have a couple of issues. First the notation "AS d, d.device_info AS l" is used to unnest arrays in your super data. You don't have any arrays to unnest so this is returning zero rows.

Second Redshift defaults to lower case for all column names so DEVICE_ID is being seen as device_id. You can enable case sensitive column names by setting the enable_case_sensitive_identifier connection variable to true and quoting all column names that require upper characters. "SET enable_case_sensitive_identifier TO true;" and changing l.DEVICE_ID to l."DEVICE_ID".

You also have unneeded layers in your query.

Putting all these together you can run:

SELECT l, l.deviceinfo, l.deviceinfo."DEVICE_ID" 
FROM (
    SELECT JSON_PARSE(file_attr) AS l
    FROM public.dc_ac_files
) AS c

You also don't need SUPER data type to perform this. This can be done with json string parsing functions.

SELECT file_attr, json_extract_path_text(file_attr, 'deviceinfo') as deviceinfo, json_extract_path_text(file_attr, 'deviceinfo','DEVICE_ID') as device_id
FROM public.dc_ac_files

Upvotes: 1

Related Questions