CODEWITHSUNDEEP
sqljsonpresto
e902d894cc
e902d894cc

Reputation: 107

Extract Nested JSON fields in SQL

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

Upvotes: 0

Views: 317

Answers (1)

Guru Stron
Guru Stron

Reputation: 142123

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]

Upvotes: 1

Related Questions