Extracting JSON data without knowing the key

Question

I'm getting a headache trying to figure out how to read a JSON with the following format in Athena

{
  "id": "1",
  "key1": {
    "dynamic_key_here": [
      {"id": "A", "key2": "content I want"}
    ]
  }
}

Let's call this column "data", and the table it's from "info"

With a SELECT json_extract(data, '$.key1') FROM info I can get to the second layer and retrieve it as a json, but here I'm stuck because the key of this second layer is dynamic, I don't know it in advance. All I need is the content of the string contained in key2 in that second layer, (it's a 1:1 relation with what's outside the json in the table, basically it's just like adding a column to my table, no unnesting needed) can anyone help?

Guru Stron · Accepted Answer · 2024-12-11 12:52:24Z

1

There are multiple ways to achieve that.

For example by using the ability to cast JSON to MAP:

-- sample data
WITH dataset(data) as (
    values ('{
          "id": "1",
          "key1": {
            "dynamic_key_here": [
              {"id": "A", "key2": "content I want"}
            ]
          }
        }')
)

-- query
select json_extract(content, '$.key2') result
from dataset
, unnest(map_values(cast(json_extract(data, '$.key1') as map(varchar, array(json))))) as t(inner_arr)
, unnest(inner_arr) as tt(content);

Output:

      result
------------------
 "content I want"

Another approach is to use json-query:

-- query
select json_query(data, 'lax $.key1.*.key2' WITH  ARRAY WRAPPER) result
from dataset;

Output:

       result
--------------------
 ["content I want"]

answered Dec 11, 2024 at 12:52

Guru Stron

151k11 gold badges187 silver badges234 bronze badges

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

Collectives™ on Stack Overflow

Extracting JSON data without knowing the key

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