Let's suppose you need to find how many cars in your car shop had a rebuilt engine, and this column is a JSON with an array of diagnosed failures
How can you do this query?
SELECT COUNT(DISTINCT(CARS)) from CARS where CARS.DIAGNOSES->>"$.diagnoses[*].failure.name" LIKE '%rebuilded engine%'
Enter fullscreen mode Exit fullscreen mode
the ->> operator in addition unquotes the extracted result. In other words, given a JSON column value column and a path expression path (a string literal)
The ->> operator can be used wherever JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes (but is not limited to) SELECT lists, WHERE and HAVING clauses, and ->>0 and ->>1 clauses.
the ->>2 indicates how you can access a json and ->>3 can search in any position of an array.
❮ PHP Array Reference
Example
Get column of last names from a recordset:
// An array that represents a possible record set returned from a database
$a = array(
array(
'id' => 5698,
'first_name' => 'Peter',
'last_name' => 'Griffin',
),
array(
'id' => 4767,
'first_name' => 'Ben',
'last_name' => 'Smith',
),
array(
'id' => 3809,
'first_name' => 'Joe',
'last_name' => 'Doe',
)
);
$last_names = array_column($a, 'last_name');
print_r($last_names);
?>
Output:
Array
(
[0] => Griffin
[1] => Smith
[2] => Doe
)
Definition and Usage
The array_column() function returns the values from a single column in the input array.
Syntax
array_column(array, column_key, index_key)
Parameter Values
ParameterDescriptionarrayRequired. Specifies the multi-dimensional array (record-set) to use. As of PHP 7.0, this can also be an array of objects.column_keyRequired. An integer key or a string key name of the column of values to return. This parameter can also be NULL to return complete arrays (useful together with index_key to re-index the array)index_keyOptional. The column to use as the index/keys for the returned arrayTechnical Details
Return Value:Returns an array of values that represents a single column from the input arrayPHP Version:5.5+More Examples
Example
Get column of last names from a recordset, indexed by the "id" column:
// An array that represents a possible record set returned from a database
$a = array(
array(
'id' => 5698,
'first_name' => 'Peter',
'last_name' => 'Griffin',
),
array(
'id' => 4767,
'first_name' => 'Ben',
'last_name' => 'Smith',
),
array(
'id' => 3809,
'first_name' => 'Joe',
'last_name' => 'Doe',
)
);
$last_names = array_column($a, 'last_name', 'id');
print_r($last_names);
?>
Output:
Array
(
[5698] => Griffin
[4767] => Smith
[3809] => Doe
)
❮ PHP Array Reference
Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.
Anda mungkin memiliki sumber data berisi string dikodekan JSON yang tidak selalu ingin 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} ] }Untuk mengekstraksi properti name dan WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset0 dari string JSON, gunakan fungsi WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset1 seperti pada contoh berikut. Fungsi WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset1 mengambil kolom yang berisi string JSON, dan mencarinya menggunakan eksprei seperti WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset3 dengan notasi titikWITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset4.
WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset3 melakukan traversal pohon sederhana. Ini menggunakan tanda WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset6 untuk menunjukkan root dari dokumen JSON, diikuti oleh titik dan elemen mest langsung di bawah root, seperti WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset7.
WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM datasetNilai 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 WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset8. Ini mirip dengan WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset1, tetapi mengembalikan hanya nilai skalar (Boolean, angka, atau string).
Jangan gunakan fungsi WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset8 pada larik, peta, atau struct.
WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract_scalar(blob, '$.name') AS name, json_extract_scalar(blob, '$.projects') AS projects FROM datasetKueri ini menghasilkan
+---------------------------+ | name | projects | +---------------------------+ | Susan Smith | | +---------------------------+Untuk mendapatkan elemen pertama dari properti WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset0 dalam larik contoh, gunakan fungsi +-----------------------------------------------------------------------------------------------+ | name | projects | +-----------------------------------------------------------------------------------------------+ | "Susan Smith" | [{"name":"project1","completed":false},{"name":"project2","completed":true}] | +-----------------------------------------------------------------------------------------------+2 dan tentukan posisi indeks.
WITH dataset AS ( SELECT '{"name": "Bob Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}' AS blob ) SELECT json_array_get(json_extract(blob, '$.projects'), 0) AS item FROM datasetIni mengembalikan nilai pada posisi indeks yang ditentukan dalam larik dikodekan JSON.
+---------------------------------------+ | item | +---------------------------------------+ | {"name":"project1","completed":false} | +---------------------------------------+Untuk menghasilkan tipe string Athena, gunakan operator +-----------------------------------------------------------------------------------------------+ | name | projects | +-----------------------------------------------------------------------------------------------+ | "Susan Smith" | [{"name":"project1","completed":false},{"name":"project2","completed":true}] | +-----------------------------------------------------------------------------------------------+3 dalam ekspresi WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset3, kemudian gunakan fungsi WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS blob ) SELECT json_extract(blob, '$.name') AS name, json_extract(blob, '$.projects') AS projects FROM dataset8. Untuk informasi selengkapnya tentang +-----------------------------------------------------------------------------------------------+ | name | projects | +-----------------------------------------------------------------------------------------------+ | "Susan Smith" | [{"name":"project1","completed":false},{"name":"project2","completed":true}] | +-----------------------------------------------------------------------------------------------+3, lihat Mengakses elemen array.