Extrair dados JSON de strings

Você pode ter dados de origem contendo strings codificadas em JSON que não deseja necessariamente desserializar em uma tabela no Athena. Neste caso, você ainda pode executar operações SQL nesses dados usando as funções JSON disponíveis no Presto.

Considere essa string JSON como um conjunto de dados de exemplo.

{"name": "Susan Smith",
"org": "engineering",
"projects":
    [
     {"name":"project1", "completed":false},
     {"name":"project2", "completed":true}
    ]
}

Exemplos: extração de propriedades

Para extrair as propriedades name e projects da string JSON, use a função json_extract como no exemplo a seguir. A função json_extract utiliza a coluna que contém a string JSON e a pesquisa usando uma expressão como JSONPath com a notação .

nota

JSONPath realiza um transversal de árvore simples. Ele usa o sinal $ para denotar a raiz do documento JSON, seguido de um ponto final e um elemento aninhado diretamente na raiz, como $.name.

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},
           {"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract(myblob, '$.name') AS name,
  json_extract(myblob, '$.projects') AS projects
FROM dataset

O valor retornado é uma string codificada em JSON, e não um tipo de dados nativo do Athena.

+-----------------------------------------------------------------------------------------------+
| name           | projects                                                                     |
+-----------------------------------------------------------------------------------------------+
| "Susan Smith"  | [{"name":"project1","completed":false},{"name":"project2","completed":true}] |
+-----------------------------------------------------------------------------------------------+

Para extrair o valor escalar da string JSON, use a função json_extract_scalar(json, json_path). É semelhante ao json_extract, mas retorna um valor de string varchar em vez de uma string codificada em JSON. O valor do parâmetro json_path deve ser um escalar (um booleano, número ou string).

nota

Não use a função json_extract_scalar em matrizes, mapas ou structs.

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract_scalar(myblob, '$.name') AS name,
  json_extract_scalar(myblob, '$.projects') AS projects
FROM dataset

Essa consulta retorna:

+---------------------------+
| name           | projects |
+---------------------------+
| Susan Smith    |          |
+---------------------------+

Para obter o primeiro elemento da propriedade projects na matriz de exemplo, use a função json_array_get e especifique a posição de índice.

WITH dataset AS (
  SELECT '{"name": "Bob Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT json_array_get(json_extract(myblob, '$.projects'), 0) AS item
FROM dataset

Ele retorna o valor na posição de índice especificada na matriz codificada em JSON.

+---------------------------------------+
| item                                  |
+---------------------------------------+
| {"name":"project1","completed":false} |
+---------------------------------------+

Para retornar um tipo de string do Athena, use o operador [] dentro de uma expressão JSONPath e use a função json_extract_scalar. Para obter mais informações sobre o [], consulte Acessar elementos de matrizes.

WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS myblob
)
SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name
FROM dataset

Ela retorna este resultado:

+--------------+
| project_name |
+--------------+
| project1     |
+--------------+