Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.
# Kueri data JSON
Amazon Athena memungkinkan Anda menanyakan data yang disandikan JSON, mengekstrak data dari JSON bersarang, mencari nilai, dan menemukan panjang dan ukuran array JSON. Untuk mempelajari dasar-dasar kueri data JSON di Athena, pertimbangkan contoh data planet berikut:
```
{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}
```
Perhatikan bagaimana setiap catatan (pada dasarnya, setiap baris dalam tabel) berada pada baris terpisah. Untuk menanyakan data JSON ini, Anda dapat menggunakan `CREATE TABLE` pernyataan seperti berikut:
```
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/'
```
Untuk query data, gunakan `SELECT` pernyataan sederhana seperti contoh berikut.
```
SELECT * FROM planets_json
```
Hasil kueri terlihat seperti berikut ini.
****
| \$1 | name | jarakdarimatahari | periode orbital | panjang hari |
| --- | --- | --- | --- | --- |
| 1 | Merkuri | 0,39 | 0,24 | 58.65 |
| 2 | Venus | 0,72 | 0,62 | 243.02 |
| 3 | Bumi | 1.0 | 1.0 | 1.0 |
| 4 | Mars | 1,52 | 1,88 | 1.03 |
Perhatikan bagaimana `CREATE TABLE` pernyataan menggunakan[OpenX JSON SerDe](openx-json-serde.md), yang mengharuskan setiap catatan JSON berada pada baris terpisah. Jika JSON dalam format cetak cantik, atau jika semua catatan berada pada satu baris, data tidak akan dibaca dengan benar.
Untuk kueri data JSON yang dalam format cetak cantik, Anda dapat menggunakan [Sarang Ion Amazon SerDe](ion-serde.md) bukan SerDe OpenX JSON. Pertimbangkan data sebelumnya yang disimpan dalam format cetak cantik:
```
{
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
}
```
Untuk menanyakan data ini tanpa memformat ulang, Anda dapat menggunakan `CREATE TABLE` pernyataan seperti berikut ini. Perhatikan bahwa, alih-alih menentukan OpenX SerDe JSON, pernyataan menentukan. `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/'
```
Kueri `SELECT * FROM planets_ion` menghasilkan hasil yang sama seperti sebelumnya. Untuk informasi selengkapnya tentang membuat tabel dengan cara ini menggunakan Amazon Ion Hive SerDe, lihat[Buat tabel Amazon Ion](ion-serde-using-create-table.md).
Contoh data JSON sebelumnya tidak berisi tipe data yang kompleks seperti array bersarang atau struct. Untuk informasi selengkapnya tentang menanyakan data JSON bersarang, lihat. [Contoh: deserialisasi JSON bersarang](openx-json-serde.md#nested-json-serde-example)
**Topics**
+ [Praktik terbaik untuk membaca data JSON](parsing-json-data.md)
+ [Ekstrak data JSON dari string](extracting-data-from-JSON.md)
+ [Cari nilai dalam array JSON](searching-for-values.md)
+ [Dapatkan panjang dan ukuran array JSON](length-and-size.md)
+ [Memecahkan masalah kueri JSON](json-troubleshooting.md)
# Praktik terbaik untuk membaca data JSON
JavaScript Object Notation (JSON) adalah metode umum untuk encoding struktur data sebagai teks. Banyak aplikasi dan alat output data yang dikodekan JSON-.
Di Amazon Athena, Anda dapat membuat tabel dari data eksternal dan menyertakan data dikodekan JSON di dalamnya. Untuk tipe data sumber seperti itu, gunakan Athena bersama [Perpustakaan JSON SerDe](json-serde.md).
Gunakan kiat berikut untuk membaca data yang dikodekan JSON:
+ Pilih yang benar SerDe, JSON asli SerDe`org.apache.hive.hcatalog.data.JsonSerDe`, atau SerDe `org.openx.data.jsonserde.JsonSerDe` OpenX,. Untuk informasi selengkapnya, lihat [Perpustakaan JSON SerDe](json-serde.md).
+ Pastikan bahwa setiap catatan yang disandikan JSON diwakili pada baris terpisah, tidak dicetak dengan cantik.
**catatan**
SerDe Mengharapkan setiap dokumen JSON berada pada satu baris teks tanpa karakter penghentian baris yang memisahkan bidang dalam catatan. Jika teks JSON dalam format cetak cantik, Anda mungkin menerima pesan kesalahan seperti HIVE\$1CURSOR\$1ERROR: Row is not a valid JSON Object or HIVE\$1CURSOR\$1ERROR:: Unexpected JsonParseException end-of-input: expected close marker for OBJECT saat Anda mencoba menanyakan tabel setelah Anda membuatnya. Untuk informasi selengkapnya, lihat [File Data JSON](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files) di dokumentasi SerDe OpenX. GitHub
+ Hasilkan data yang dikodekan JSON Anda dalam kolom peka huruf.
+ Beri opsi untuk mengabaikan catatan dengan bentuk yang salah, seperti dalam contoh ini.
```
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/';
```
+ Konversi bidang dalam data sumber yang memiliki skema yang belum ditentukan ke string yang dikodekan JSON di Athena.
Saat Athena membuat tabel yang didukung oleh data JSON, Athena akan menguraikan data berdasarkan skema yang ada dan yang ditentukan sebelumnya. Namun, tidak semua data Anda mungkin memiliki skema yang ditentukan sebelumnya. Untuk menyederhanakan manajemen skema dalam kasus tersebut, ini sering berguna untuk mengonversi bidang dalam sumber data yang memiliki skema yang belum ditentukan untuk string JSON di Athena, kemudian menggunakan [Perpustakaan JSON SerDe](json-serde.md).
Sebagai contoh, pertimbangkan sebuah aplikasi IOT yang mempublikasikan peristiwa dengan bidang umum dari sensor yang berbeda. Salah satu bidang tersebut harus menyimpan muatan kustom yang unik untuk sensor yang mengirim peristiwa. Dalam hal ini, karena Anda tidak tahu skema, kami sarankan Anda menyimpan informasi sebagai string dikodekan JSON. Untuk melakukan ini, konversi data dalam tabel Athena Anda ke JSON, seperti dalam contoh berikut. Anda juga dapat mengonversi data yang dikodekan JSON ke tipe data Athena.
**Topics**
+ [Mengkonversi tipe data Athena ke JSON](converting-native-data-types-to-json.md)
+ [Mengkonversi tipe data JSON ke Athena](converting-json-to-native-data-types.md)
# Mengkonversi tipe data Athena ke JSON
Untuk mengonversi tipe data Athena ke JSON, gunakan `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
```
Kueri ini menghasilkan:
```
+-------------------------------------------+
| hello_msg | some_int | some_map |
+-------------------------------------------+
| "HELLO ATHENA" | 12345 | {"a":1,"b":2} |
+-------------------------------------------+
```
# Mengkonversi tipe data JSON ke Athena
Untuk mengonversi tipe data JSON ke Athena, gunakan `CAST`.
**catatan**
Dalam contoh ini, untuk menunjukkan string sebagai dikodekan JSON, mulai dengan `JSON` dan gunakan tanda kutip tunggal, seperti `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
```
Kueri ini menghasilkan:
```
+-------------------------------------+
| hello_msg | some_int | some_map |
+-------------------------------------+
| HELLO ATHENA | 12345 | {a:1,b:2} |
+-------------------------------------+
```
# Ekstrak data JSON dari string
Anda mungkin memiliki data sumber yang berisi string yang dikodekan JSON yang tidak perlu Anda deserialisasi ke dalam tabel di Athena. Dalam hal ini, Anda masih dapat menjalankan operasi SQL pada data ini, menggunakan fungsi JSON yang tersedia di Presto.
Pertimbangkan string JSON ini sebagai contoh set data.
```
{"name": "Susan Smith",
"org": "engineering",
"projects":
[
{"name":"project1", "completed":false},
{"name":"project2", "completed":true}
]
}
```
## Contoh: Ekstrak properti
Untuk mengekstraksi properti `name` dan `projects` dari string JSON, gunakan fungsi `json_extract` seperti pada contoh berikut. Fungsi `json_extract` mengambil kolom yang berisi string JSON, dan mencarinya menggunakan eksprei seperti `JSONPath` dengan notasi titik`.`.
**catatan**
`JSONPath` melakukan traversal pohon sederhana. Ini menggunakan tanda `$` untuk menunjukkan root dari dokumen JSON, diikuti oleh titik dan elemen mest langsung di bawah root, seperti `$.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
```
Nilai yang dihasilkan adalah string dikodekan JSON, dan bukan tipe data Athena asli.
```
+-----------------------------------------------------------------------------------------------+
| name | projects |
+-----------------------------------------------------------------------------------------------+
| "Susan Smith" | [{"name":"project1","completed":false},{"name":"project2","completed":true}] |
+-----------------------------------------------------------------------------------------------+
```
Untuk mengekstraksi nilai skalar dari string JSON, gunakan fungsi `json_extract_scalar(json, json_path)`. Hal ini mirip dengan`json_extract`, tetapi mengembalikan nilai `varchar` string bukan string JSON-encoded. Nilai untuk *json\$1path* parameter harus skalar (Boolean, angka, atau string).
**catatan**
Jangan gunakan fungsi `json_extract_scalar` pada larik, peta, atau struct.
```
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
```
Kueri ini menghasilkan
```
+---------------------------+
| name | projects |
+---------------------------+
| Susan Smith | |
+---------------------------+
```
Untuk mendapatkan elemen pertama dari properti `projects` dalam larik contoh, gunakan fungsi `json_array_get` dan tentukan posisi indeks.
```
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
```
Ini mengembalikan nilai pada posisi indeks yang ditentukan dalam larik dikodekan JSON.
```
+---------------------------------------+
| item |
+---------------------------------------+
| {"name":"project1","completed":false} |
+---------------------------------------+
```
Untuk menghasilkan tipe string Athena, gunakan operator `[]` dalam ekspresi `JSONPath`, kemudian gunakan fungsi `json_extract_scalar`. Untuk informasi selengkapnya tentang `[]`, lihat [Mengakses elemen array](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
```
Ini mengembalikan hasil ini:
```
+--------------+
| project_name |
+--------------+
| project1 |
+--------------+
```
# Cari nilai dalam array JSON
Untuk menentukan apakah nilai tertentu ada dalam larik dikodekan JSON, gunakan fungsi `json_array_contains`.
Kueri berikut mencantumkan nama-nama pengguna yang berpartisipasi dalam “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')
```
Kueri ini mengembalikan daftar pengguna.
```
+-------------+
| user |
+-------------+
| Susan Smith |
+-------------+
| Jane Smith |
+-------------+
```
Contoh kueri berikut mencantumkan nama pengguna yang telah menyelesaikan proyek bersama dengan jumlah total proyek yang telah selesai. Ini melakukan tindakan ini:
+ Menggunakan pernyataan nested `SELECT` untuk kejelasan.
+ Mengekstraksi larik proyek.
+ Mengonversi larik ke larik asli pasangan nilai kunci menggunakan `CAST`.
+ Mengekstraksi setiap elemen larik menggunakan operator `UNNEST`.
+ Filter memperoleh nilai dengan menyelesaikan proyek dan menghitungnya.
**catatan**
Saat menggunakan `CAST` untuk `MAP`, Anda dapat menentukan elemen kunci sebagai `VARCHAR` (String asli di Presto), tetapi meninggalkan nilai sebagai JSON, karena nilai-nilai dalam `MAP`adalah dari berbagai tipe: String untuk pasangan nilai kunci pertama, dan Boolean untuk kedua.
```
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
```
Kueri berikut mengembalikan hasil ini:
```
+----------------------------------+
| name | completed_projects |
+----------------------------------+
| Susan Smith | 2 |
+----------------------------------+
| Jane Smith | 1 |
+----------------------------------+
```
# Dapatkan panjang dan ukuran array JSON
Untuk mendapatkan panjang dan ukuran array JSON, Anda dapat menggunakan `json_array_length` dan `json_size` fungsi.
## Contoh: `json_array_length`
Untuk mendapatkan panjang larik dikodekan JSON, gunakan fungsi `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
```
Kueri ini mengembalikan hasil ini:
```
+---------------------+
| name | count |
+---------------------+
| Susan Smith | 2 |
+---------------------+
| Bob Smith | 1 |
+---------------------+
| Jane Smith | 1 |
+---------------------+
```
## Contoh: `json_size`
Untuk mendapatkan ukuran larik atau objek dikodekan JSON, gunakan fungsi `json_size` dan tentukan kolom yang berisi string JSON dan ekspresi `JSONPath` untuk larik atau objek.
```
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
```
Kueri ini mengembalikan hasil ini:
```
+---------------------+
| name | count |
+---------------------+
| Susan Smith | 2 |
+---------------------+
| Bob Smith | 1 |
+---------------------+
| Jane Smith | 1 |
+---------------------+
```
# Memecahkan masalah kueri JSON
Untuk bantuan pemecahan masalah dengan kueri terkait JSON, lihat [Kesalahan terkait JSON](troubleshooting-athena.md#troubleshooting-athena-json-related-errors) atau lihat sumber daya berikut:
+ [Saya mendapatkan kesalahan ketika saya mencoba membaca data JSON di Amazon Athena](https://aws.amazon.com/premiumsupport/knowledge-center/error-json-athena/)
+ [Bagaimana cara mengatasi “HIVE\$1CURSOR\$1ERROR: Baris bukan objek JSON yang valid - JSONException: Kunci duplikat” saat membaca file dari Athena? AWS Config](https://aws.amazon.com/premiumsupport/knowledge-center/json-duplicate-key-error-athena-config/)
+ [Kueri SELECT COUNT di Amazon Athena hanya mengembalikan satu catatan meskipun file JSON input memiliki banyak catatan](https://aws.amazon.com/premiumsupport/knowledge-center/select-count-query-athena-json-records/)
+ [Bagaimana saya bisa melihat file sumber Amazon S3 untuk satu baris di tabel Athena?](https://aws.amazon.com/premiumsupport/knowledge-center/find-s3-source-file-athena-table-row/)
Lihat juga [Pertimbangan dan batasan untuk kueri SQL di Amazon Athena](other-notable-limitations.md).