# Consultar dados JSON
<a name="querying-JSON"></a>

O Amazon Athena permite analisar dados codificados em JSON, extrair dados JSON aninhado, pesquisar valores e saber o comprimento e o tamanho dos arrays JSON. Para aprender os fundamentos da consulta de dados JSON no Athena, considere os seguintes exemplos de dados do planeta:

```
{name:"Mercury",distanceFromSun:0.39,orbitalPeriod:0.24,dayLength:58.65}
{name:"Venus",distanceFromSun:0.72,orbitalPeriod:0.62,dayLength:243.02}
{name:"Earth",distanceFromSun:1.00,orbitalPeriod:1.00,dayLength:1.00}
{name:"Mars",distanceFromSun:1.52,orbitalPeriod:1.88,dayLength:1.03}
```

Observe como cada registro (basicamente, cada linha na tabela) está em uma linha diferente. Para consultar os dados JSON, você pode usar uma declaração `CREATE TABLE` como a seguinte:

```
CREATE EXTERNAL TABLE `planets_json`(
  `name` string,
  `distancefromsun` double,
  `orbitalperiod` double,
  `daylength` double)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  's3://amzn-s3-demo-bucket/json/'
```

Para consultar os dados, use uma declaração `SELECT` como a seguinte.

```
SELECT * FROM planets_json
```

Os resultados da consulta são semelhantes aos seguintes:


****  

| \$1 | name | distância do sol | período orbital | duração do dia | 
| --- | --- | --- | --- | --- | 
| 1 | Mercúrio | 0,39 | 0,24 | 58,65 | 
| 2 | Vênus | 0,72 | 0,62 | 243,02 | 
| 3 | Terra | 1.0 | 1.0 | 1,0 | 
| 4 | Marte | 1,52 | 1,88 | 1,03 | 

Observe como a declaração `CREATE TABLE` usa [OpenX JSON SerDe](openx-json-serde.md), o que exige que cada registro JSON esteja em uma linha diferente. Se o JSON estiver em um formato pretty print ou se todos os registros estiverem em uma única linha, os dados não serão lidos corretamente.

Para consultar dados JSON que estejam em um formato pretty print, você pode usar [Amazon Ion Hive SerDe](ion-serde.md) em vez do OpenX JSON SerDe. Considere os dados anteriores armazenados em um formato pretty print:

```
{
  name:"Mercury",
  distanceFromSun:0.39,
  orbitalPeriod:0.24,
  dayLength:58.65
}
{
  name:"Venus",
  distanceFromSun:0.72,
  orbitalPeriod:0.62,
  dayLength:243.02
}
{
  name:"Earth",
  distanceFromSun:1.00,
  orbitalPeriod:1.00,
  dayLength:1.00
}
{
  name:"Mars",
  distanceFromSun:1.52,
  orbitalPeriod:1.88,
  dayLength:1.03
}
```

Para consultar esses dados sem reformatar, você pode usar uma declaração `CREATE TABLE` como a seguinte. Observe que, em vez de especificar o OpenX JSON SerDe, a instrução especifica `STORED AS ION`. 

```
CREATE EXTERNAL TABLE `planets_ion`(
  `name` string,
  `distancefromsun` DECIMAL(10, 2),
  `orbitalperiod` DECIMAL(10, 2),
  `daylength` DECIMAL(10, 2))
STORED AS ION
LOCATION
  's3://amzn-s3-demo-bucket/json-ion/'
```

A consulta `SELECT * FROM planets_ion` produz os mesmos resultados de antes. Para obter mais informações sobre a criação de tabelas dessa forma usando o Amazon Ion Hive SerDe, consulte [Criar tabelas do Amazon Ion](ion-serde-using-create-table.md).

O exemplo anterior de dados JSON não contém tipos de dados complexos, como arrays ou estruturas aninhadas. Para obter mais informações sobre como consultar dados JSON aninhado, consulte [Exemplo: desserializar JSON aninhado](openx-json-serde.md#nested-json-serde-example).

**Topics**
+ [

# Práticas recomendadas de leitura de dados JSON
](parsing-json-data.md)
+ [

# Extrair dados JSON de strings
](extracting-data-from-JSON.md)
+ [

# Pesquisar por valores em matrizes JSON
](searching-for-values.md)
+ [

# Obter comprimento e tamanho de matrizes JSON
](length-and-size.md)
+ [

# Solução de problemas de consultas JSON
](json-troubleshooting.md)

# Práticas recomendadas de leitura de dados JSON
<a name="parsing-json-data"></a>

JavaScript Object Notation (JSON) é um método comum para codificar estruturas de dados como texto. Muitos aplicativos e ferramentas produzem dados codificados em JSON.

No Amazon Athena, você pode criar tabelas com base em dados externos e incluir dados codificados em JSON nelas. Para esses tipos de dados de origem, use o Athena junto com [Bibliotecas SerDe JSON](json-serde.md). 

Use as seguintes dicas para ler dados codificados por JSON:
+ Escolha o SerDe certo: um JSON SerDe nativo, `org.apache.hive.hcatalog.data.JsonSerDe`; ou um OpenX SerDe, `org.openx.data.jsonserde.JsonSerDe`. Para obter mais informações, consulte [Bibliotecas SerDe JSON](json-serde.md).
+ Certifique-se de que cada registro codificado em JSON seja representado em uma linha separada, não formatado para impressão.
**nota**  
O SerDe espera que cada documento JSON esteja em uma única linha de texto, sem caracteres de terminação de linha separando os campos no registro. Se o texto JSON estiver formatado para impressão, você poderá receber uma mensagem de erro como HIVE\$1CURSOR\$1ERROR: Row is not a valid JSON Object (HIVE\$1CURSOR\$1ERROR: a linha não é um objeto JSON válido) ou HIVE\$1CURSOR\$1ERROR: JsonParseException: Unexpected end-of-input: expected close marker for OBJECT (HIVE\$1CURSOR\$1ERROR: JSONParseException: Fim de entrada inesperado: marcador de fechamento esperado para OBJECT) quando tentar consultar a tabela após criá-la. Para obter mais informações, consulte [JSON Data Files](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files) na documentação do OpenX SerDe no GitHub. 
+ Gere seus dados codificados por JSON em colunas sem distinção entre letras maiúsculas e minúsculas.
+ Forneça uma opção para ignorar registros malformadas, como neste exemplo.

  ```
  CREATE EXTERNAL TABLE json_table (
    column_a string,
    column_b int
   )
   ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
   WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true')
   LOCATION 's3://amzn-s3-demo-bucket/path/';
  ```
+ Converta os campos nos dados de origem que tenham um esquema indeterminado em strings codificadas em JSON no Athena.

Ao criar tabelas com os dados do JSON, o Athena analisa os dados com base no esquema existente e predefinido. No entanto, nem todos os dados podem ter um esquema predefinido. Para simplificar o gerenciamento de esquemas nesses casos, costuma ser útil converter os campos nos dados de origem que têm um esquema indeterminado em strings JSON no Athena e usar [Bibliotecas SerDe JSON](json-serde.md).

Por exemplo, considere um aplicativo IoT que publique eventos com campos comuns de sensores diferentes. Um desses campos deve armazenar uma carga útil personalizada que seja exclusiva do sensor que envia o evento. Nesse caso, como você não sabe o esquema, recomendamos armazenar as informações como uma string codificada em JSON. Para isso, converta os dados na tabela do Athena em JSON, como no exemplo a seguir. Você também pode converter os dados codificados em JSON em tipos de dados do Athena.

**Topics**
+ [

# Converter tipos de dados do Athena em JSON
](converting-native-data-types-to-json.md)
+ [

# Converter JSON em tipos de dados do Athena
](converting-json-to-native-data-types.md)

# Converter tipos de dados do Athena em JSON
<a name="converting-native-data-types-to-json"></a>

Para converter os tipos de dados do Athena em JSON, use `CAST`.

```
WITH dataset AS (
  SELECT
    CAST('HELLO ATHENA' AS JSON) AS hello_msg,
    CAST(12345 AS JSON) AS some_int,
    CAST(MAP(ARRAY['a', 'b'], ARRAY[1,2]) AS JSON) AS some_map
)
SELECT * FROM dataset
```

Essa consulta retorna:

```
+-------------------------------------------+
| hello_msg      | some_int | some_map      |
+-------------------------------------------+
| "HELLO ATHENA" | 12345    | {"a":1,"b":2} |
+-------------------------------------------+
```

# Converter JSON em tipos de dados do Athena
<a name="converting-json-to-native-data-types"></a>

Para converter os dados do JSON em tipos de dados do Athena, use `CAST`.

**nota**  
Neste exemplo, para denotar strings como codificadas em JSON, comece com a palavra-chave `JSON` e use aspas simples, como `JSON '12345'` 

```
WITH dataset AS (
  SELECT
    CAST(JSON '"HELLO ATHENA"' AS VARCHAR) AS hello_msg,
    CAST(JSON '12345' AS INTEGER) AS some_int,
    CAST(JSON '{"a":1,"b":2}' AS MAP(VARCHAR, INTEGER)) AS some_map
)
SELECT * FROM dataset
```

Essa consulta retorna:

```
+-------------------------------------+
| hello_msg    | some_int | some_map  |
+-------------------------------------+
| HELLO ATHENA | 12345    | {a:1,b:2} |
+-------------------------------------+
```

# Extrair dados JSON de strings
<a name="extracting-data-from-JSON"></a>

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
<a name="examples-extracting-properties"></a>

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\$1path* 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](accessing-array-elements.md).

```
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     |
+--------------+
```

# Pesquisar por valores em matrizes JSON
<a name="searching-for-values"></a>

Para determinar se um valor específico existe dentro de uma matriz codificada em JSON, use a função `json_array_contains`.

A consulta a seguir lista os nomes dos usuários que estão participando de "project2".

```
WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal", "projects": ["project1"]}'),
    (JSON '{"name": "Susan Smith", "org": "engineering", "projects": ["project1", "project2", "project3"]}'),
    (JSON '{"name": "Jane Smith", "org": "finance", "projects": ["project1", "project2"]}')
  ) AS t (users)
)
SELECT json_extract_scalar(users, '$.name') AS user
FROM dataset
WHERE json_array_contains(json_extract(users, '$.projects'), 'project2')
```

Essa consulta retorna uma lista de usuários.

```
+-------------+
| user        |
+-------------+
| Susan Smith |
+-------------+
| Jane Smith  |
+-------------+
```

O exemplo de consulta a seguir lista os nomes de usuários que concluíram projetos com o número total de projetos realizados. Ele realiza estas ações:
+ Usa instruções `SELECT` aninhadas para fins de clareza.
+ Extrai a matriz de projetos.
+ Converte a matriz em uma matriz nativa de pares de chave/valor usando `CAST`.
+ Extrai cada elemento de matriz individual usando o operador `UNNEST`.
+ Filtra valores obtidos por projetos concluídos e os conta.

**nota**  
Ao usar `CAST` em `MAP`, você pode especificar o elemento de chave como `VARCHAR` (String nativa no Presto), mas deixar o valor como JSON, porque os valores no `MAP` são de tipos diferentes: string para o primeiro par de chave/valor e Booliano para o segundo.

```
WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith",
             "org": "legal",
             "projects": [{"name":"project1", "completed":false}]}'),
    (JSON '{"name": "Susan Smith",
             "org": "engineering",
             "projects": [{"name":"project2", "completed":true},
                          {"name":"project3", "completed":true}]}'),
    (JSON '{"name": "Jane Smith",
             "org": "finance",
             "projects": [{"name":"project2", "completed":true}]}')
  ) AS t (users)
),
employees AS (
  SELECT users, CAST(json_extract(users, '$.projects') AS
    ARRAY(MAP(VARCHAR, JSON))) AS projects_array
  FROM dataset
),
names AS (
  SELECT json_extract_scalar(users, '$.name') AS name, projects
  FROM employees, UNNEST (projects_array) AS t(projects)
)
SELECT name, count(projects) AS completed_projects FROM names
WHERE cast(element_at(projects, 'completed') AS BOOLEAN) = true
GROUP BY name
```

Esta consulta retorna o seguinte resultado:

```
+----------------------------------+
| name        | completed_projects |
+----------------------------------+
| Susan Smith | 2                  |
+----------------------------------+
| Jane Smith  | 1                  |
+----------------------------------+
```

# Obter comprimento e tamanho de matrizes JSON
<a name="length-and-size"></a>

Para obter o comprimento e o tamanho de matrizes JSON, é possível usar as funções `json_array_length` e `json_size`.

## Exemplo: `json_array_length`
<a name="example-json-array-length"></a>

Para obter o tamanho de uma matriz codificada em JSON, use a função `json_array_length`.

```
WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name":
            "Bob Smith",
            "org":
            "legal",
            "projects": [{"name":"project1", "completed":false}]}'),
    (JSON '{"name": "Susan Smith",
            "org": "engineering",
            "projects": [{"name":"project2", "completed":true},
                         {"name":"project3", "completed":true}]}'),
    (JSON '{"name": "Jane Smith",
             "org": "finance",
             "projects": [{"name":"project2", "completed":true}]}')
  ) AS t (users)
)
SELECT
  json_extract_scalar(users, '$.name') as name,
  json_array_length(json_extract(users, '$.projects')) as count
FROM dataset
ORDER BY count DESC
```

Essa consulta retorna este resultado:

```
+---------------------+
| name        | count |
+---------------------+
| Susan Smith | 2     |
+---------------------+
| Bob Smith   | 1     |
+---------------------+
| Jane Smith  | 1     |
+---------------------+
```

## Exemplo: `json_size`
<a name="example-json-size"></a>

Para obter o tamanho de uma matriz ou de um objeto codificado em JSON, use a função `json_size` e especifique a coluna que contém a string JSON e a expressão `JSONPath` para a matriz ou o objeto.

```
WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'),
    (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true},{"name":"project3", "completed":true}]}'),
    (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}')
  ) AS t (users)
)
SELECT
  json_extract_scalar(users, '$.name') as name,
  json_size(users, '$.projects') as count
FROM dataset
ORDER BY count DESC
```

Essa consulta retorna este resultado:

```
+---------------------+
| name        | count |
+---------------------+
| Susan Smith | 2     |
+---------------------+
| Bob Smith   | 1     |
+---------------------+
| Jane Smith  | 1     |
+---------------------+
```

# Solução de problemas de consultas JSON
<a name="json-troubleshooting"></a>

Para obter ajuda sobre como solucionar problemas com consultas relacionadas ao JSON, leia [Erros relacionados ao JSON](troubleshooting-athena.md#troubleshooting-athena-json-related-errors) ou acesse os seguintes recursos:
+ [Recebo mensagens de erro ao tentar ler dados JSON no Amazon Athena](https://aws.amazon.com/premiumsupport/knowledge-center/error-json-athena/)
+ [Como resolver o erro “HIVE\$1CURSOR\$1ERROR: A linha não é um objeto JSON válido - JSONException: Chave duplicada” ao ler arquivos do AWS Config no Athena?)](https://aws.amazon.com/premiumsupport/knowledge-center/json-duplicate-key-error-athena-config/)
+ [A consulta SELECT COUNT no Amazon Athena retorna somente um registro, embora o arquivo JSON de entrada tenha vários registros](https://aws.amazon.com/premiumsupport/knowledge-center/select-count-query-athena-json-records/)
+ [Como posso ver o arquivo de origem do Amazon S3 para uma linha em uma tabela do Athena?](https://aws.amazon.com/premiumsupport/knowledge-center/find-s3-source-file-athena-table-row/)

Consulte também [Considerações e limitações das consultas SQL no Amazon Athena](other-notable-limitations.md).