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 denganjson_extract, tetapi mengembalikan nilai varchar string bukan string JSON-encoded. Nilai untuk json_path 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.

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