Ekstrak JSON data dari string

Anda mungkin memiliki data sumber yang berisi string JSON -encode yang tidak perlu Anda deserialisasi ke dalam tabel di Athena. Dalam hal ini, Anda masih dapat menjalankan SQL operasi pada data ini, menggunakan JSON fungsi yang tersedia di Presto.

Pertimbangkan JSON string ini sebagai contoh dataset.

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

Contoh: Ekstrak properti

Untuk mengekstrak name dan projects properti dari JSON string, gunakan json_extract fungsi seperti pada contoh berikut. json_extractFungsi mengambil kolom yang berisi JSON string, dan mencarinya menggunakan ekspresi JSONPath -like dengan notasi titik..

catatan

JSONPath melakukan traversal pohon sederhana. Ini menggunakan $ tanda untuk menunjukkan akar JSON dokumen, diikuti oleh periode dan elemen bersarang 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 dikembalikan adalah string JSON -encoded, dan bukan tipe data Athena asli.

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

Untuk mengekstrak nilai skalar dari JSON string, gunakan json_extract_scalar(json, json_path) fungsi. 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

Ia mengembalikan nilai pada posisi indeks tertentu dalam array JSON -encoded.

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