Extract Nested JSON fields in SQL

Question

I am trying to write a sql query (In Presto) to extract the readable text in the details field unable to get the correct path

 {
      "2177f7f1-0d36-4663-b190-b686ab2d9031":{
         "button_name":"No Issue",
         "details":{
            "2abe85b0-5dee-49be-809f-448444af55c4":"No Issue Due to XYZ"
         }
      },
      "b2a6aba8-abe4-4ded-928f-af96eee675ef":{
         "button_name":"No Issue",
         "details":{
            "b40382f7-bf8a-477a-9f38-126d7d0016c4":"Double Blind Confirmed"
         }
      }
   }

This is what I tried but does not work SELECT json_extract_scalar(content, '$.details') FROM table

Guru Stron · Accepted Answer · 2021-11-09 12:01:19Z

Assuming the example json is the one in the content column you can use the presto's ability to transform json into MAP and do something like this:

WITH dataset (json_str) AS (
    VALUES (JSON ' {
      "2177f7f1-0d36-4663-b190-b686ab2d9031":{
         "button_name":"No Issue",
         "details":{
            "2abe85b0-5dee-49be-809f-448444af55c4":"No Issue Due to XYZ"
         }
      },
      "b2a6aba8-abe4-4ded-928f-af96eee675ef":{
         "button_name":"No Issue",
         "details":{
            "b40382f7-bf8a-477a-9f38-126d7d0016c4":"Double Blind Confirmed"
         }
      }
   }')
)

SELECT flatten(
        transform(
            map_values(cast(json_str as MAP(VARCHAR, JSON))),
            j -> map_values(
                     cast(json_extract(j, '$.details') as MAP(VARCHAR, VARCHAR))
            )
        )
    )
FROM dataset

Which will yield the next output:

_col0
[No Issue Due to XYZ, Double Blind Confirmed]

Collectives™ on Stack Overflow

Extract Nested JSON fields in SQL

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