Cara menggunakan select json array mysql

Question

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

Table of Contents Show

Definition and Usage
Parameter Values
Technical Details
More Examples

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 array

Technical 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 dataset

0 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 dataset

1 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 dataset

1 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 dataset

3 dengan notasi titik

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 dataset

4.

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 dataset

3 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 dataset

6 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 dataset

7.

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

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 dataset

8. 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 dataset

1, 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 dataset

8 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 dataset

Kueri 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 dataset

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

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

3, 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 dataset

8. Untuk informasi selengkapnya tentang

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

3, lihat Mengakses elemen array.

kode mysql