ホームページ  >  記事  >  データベース  >  mysqlでjson_extractを使用する方法

mysqlでjson_extractを使用する方法

WBOY
WBOY転載
2023-05-31 16:58:161763ブラウズ
    ##1. はじめに

    mysql5.7 バージョンは JSON 型フィールドのサポートを開始しました

    json_extract は完全に
    -> と省略できます; json_unquote(json_extract()) は
    ->> と完全に省略できます。以下のほとんどの紹介では

    という省略形が使用されます。 2. サンプルテーブルの作成

    CREATE TABLE `test_json` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `content` json DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
    # 插入两条测试用的记录
    INSERT INTO `test_json` (`content`) VALUES ('{\"name\":\"tom\",\"age\":18,\"score\":[100,90,87],\"address\":{\"province\":\"湖南\",\"city\":\"长沙\"}}');
    INSERT INTO `test_json` (`content`) VALUES ('[1, "apple", "red", {"age": 18, "name": "tom"}]');

    idcontent1{"年齢": 18、"名前": "トム"、"スコア": [100, 90, 87], "住所": {"都市": "長沙"、"省": "湖南"}} 2[1, “リンゴ”, “レッド”, {“年齢”: 18, “名前”: “トム”}]
    3. 基本構文

    - JSON オブジェクトのキーに対応する値を取得します

    • json_extract 関数では、最初の 1 つのパラメーターの内容は json データを表し、2 番目のパラメーターは json パスです。$ は json データ自体を表し、$.name は json

      # のキー名を持つ値の取得を表します。
    • ## json_extract の代わりに -> 式を使用できます
    • 取得した val 自体が文字列の場合、取得した val は " のように引用符で囲まれます。 tom" の場合、この種のデータはプログラム オブジェクトに解析されるときに、\"tom\" としてエスケープされる可能性があります。この問題を解決するには、json_unquote 関数の別の層を外側でラップするか、->
    content:
    { の代わりに ->> を使用します。 " 年齢": 18、 "名前": "トム"、 "スコア": [100, 90, 87]、 "住所": {"都市": "長沙"、 "省": "湖南"}}


    # 得到"tom"
    select json_extract(content,'$.name') from test_json where id = 1;
    # 简写方式:字段名->表达式等价于json_extract(字段名,表达式)
    select content->'$.name' from test_json where id = 1;
    # 结果:
    +--------------------------------+
    | json_extract(content,'$.name') |
    +--------------------------------+
    | "tom"                          |
    +--------------------------------+
    +-------------------+
    | content->'$.name' |
    +-------------------+
    | "tom"             |
    +-------------------+
    
    # 解除双引号,得到tom
    select json_unquote(json_extract(content,'$.name')) from test_json where id = 1;
    # 简写方式:字段名->>表达式等价于json_unquote(json_extract(字段名,表达式))
    select content->>'$.name' from test_json where id = 1;
    # 结果:
    +----------------------------------------------+
    | json_unquote(json_extract(content,'$.name')) |
    +----------------------------------------------+
    | tom                                          |
    +----------------------------------------------+
    +--------------------+
    | content->>'$.name' |
    +--------------------+
    | tom                |
    +--------------------+

    - JSON 配列の要素を取得する

      json_extract 関数では、最初のパラメーターの内容は json データを表し、2 番目のパラメーターは json パスです。ここで、 $ は json データそのものを表し、 $[i] は json 配列のインデックス i の要素を取得することを示します (インデックスは 0 から始まります)
    • は key-val の取得と同じです, 取得された要素が文字列の場合、デフォルトの方法では二重引用符で囲まれた文字も取得され、プログラムがエスケープされます。この方法では、json_unquote 関数を使用するか、-># の代わりに ->> を使用します。
    • #content:
    [1, "リンゴ", "レッド", {"年齢": 18, "名前": "トム"}]

    # 得到"apple"
    select json_extract(content,'$[1]') from test_json where id = 2;
    # 简写,效果同上
    select content->'$[1]' from test_json where id = 2;
    # 结果:
    +------------------------------+
    | json_extract(content,'$[1]') |
    +------------------------------+
    | "apple"                      |
    +------------------------------+
    +-----------------+
    | content->'$[1]' |
    +-----------------+
    | "apple"         |
    +-----------------+
    
    # 解除双引号,得到apple 
    select json_unquote(json_extract(content,'$[1]')) from test_json where id = 2;
    # 简写,效果同上
    select content->>'$[1]' from test_json where id = 2;
    # 结果:
    +--------------------------------------------+
    | json_unquote(json_extract(content,'$[1]')) |
    +--------------------------------------------+
    | apple                                      |
    +--------------------------------------------+
    +------------------+
    | content->>'$[1]' |
    +------------------+
    | apple            |
    +------------------+

    - JSON ネストデータの取得

    先ほど紹介した 2 つの取得方法と組み合わせると、json データでネストされたデータを取得できます

    content: id=1

    {"年齢": 18 、"名前": "トム"、"スコア": [100, 90, 87], "住所": {"都市": "長沙"、"省": "湖南"}}
    content: id= 2

    [1, "リンゴ", "レッド", {"年齢": 18, "名前": "トム"}]

    # 得到:87
    select content->'$.score[2]' from test_json where id = 1;
    # 结果:
    +-----------------------+
    | content->'$.score[2]' |
    +-----------------------+
    | 87                    |
    +-----------------------+
    
    # 得到:18
    select content->'$[3].age' from test_json where id = 2;
    # 结果:
    +---------------------+
    | content->'$[3].age' |
    +---------------------+
    | 18                  |
    +---------------------+

    4.改善中

    - 複数のパスの JSON データを取得する

    複数のパスのデータを配列に結合して、

    content: id=1

    を返します。 {"年齢": 18、"名前": "トム"、"スコア": [100, 90, 87]、"住所": {"都市": "長沙"、"省": "湖南"}}

    select json_extract(content,'$.age','$.score') from test_json where id = 1;
    # 结果:
    +-----------------------------------------+
    | json_extract(content,'$.age','$.score') |
    +-----------------------------------------+
    | [18, [100, 90, 87]]                     |
    +-----------------------------------------+
    
    select json_extract(content,'$.name','$.address.province','$.address.city') from test_json where id = 1;
    # 结果:
    +----------------------------------------------------------------------+
    | json_extract(content,'$.name','$.address.province','$.address.city') |
    +----------------------------------------------------------------------+
    | ["tom", "湖南", "长沙"]                                              |
    +----------------------------------------------------------------------+

    - パス式 *

    を使用すると、複数のパスのデータが配列に結合され、

    # 先插入一条用于测试的数据
    INSERT INTO `test_json` (`id`,`content`) VALUES(3,'{"name":"tom","address":{"name":"中央公园","city":"长沙"},"class":{"id":3,"name":"一年三班"},"friend":[{"age":20,"name":"marry"},{"age":21,"name":"Bob"}]}')

    content: id=3# が返されます。 ##{“名前 ": "トム", "クラス": {"id": 3, "名前": "一年三組"}, "友達": [{"年齢": 20, "名前": "結婚"}、{"年齢": 21、"名前": "ボブ"}]、"住所": {"都市": "長沙"、"名前": "セントラルパーク"}}

    # 获取所有二级嵌套中key=name的值
    # 由于friend的二级嵌套是一个数组,所以.name获取不到其中的所有name值
    select content->'$.*.name' from test_json where id = 3;
    +----------------------------------+
    | content->'$.*.name'              |
    +----------------------------------+
    | ["一年三班", "中央公园"]         |
    +----------------------------------+```
    
    # 获取所有key为name值的数据,包括任何嵌套内的name
    select content->'$**.name' from test_json where id = 3;
    +---------------------------------------------------------+
    | content->'$**.name'                                     |
    +---------------------------------------------------------+
    | ["tom", "一年三班", "marry", "Bob", "中央公园"]         |
    +---------------------------------------------------------+
    
    # 获取数组中所有的name值
    select content->'$.friend[*].name' from test_json where id = 3;
    +-----------------------------+
    | content->'$.friend[*].name' |
    +-----------------------------+
    | ["marry", "Bob"]            |
    +-----------------------------+

    - NULL 値を返します

    content: id=1

    {"age": 18, "name": "tom", "score": [100, 90, 87], "address" ": {"city": "Changsha", "province": "Hunan"}}

    お探しの JSON パスは存在しません

    # age路径不存在,返回NULL
    # 若有多个路径,只要有一个路径存在则不会返回NULL
    select json_extract(content,'$.price') from test_json where id = 1;
    +---------------------------------+
    | json_extract(content,'$.price') |
    +---------------------------------+
    | NULL                            |
    +---------------------------------+

    パスに NULL
    があります
    # 存在任意路径为NULL则返回NULL
    select json_extract(content,'$.age',NULL) from test_json where id = 1;
    +------------------------------------+
    | json_extract(content,'$.age',NULL) |
    +------------------------------------+
    | NULL                               |
    +------------------------------------+

    - エラーを返します

    最初のパラメータが JSON 型データでない場合、エラーを返します

    select json_extract('{1,2]',$[0])

    パス式が標準化されていない場合, return error

    select content->'$age' from test_json where id = 1;
    # 结果:
    ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 1.

    5. 利用シーン

    JSON_EXTRACT関数は通常、JSONで特定のデータを取得したり、判定条件として使用したい場合に使用します

    以上がmysqlでjson_extractを使用する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

    声明:
    この記事はyisu.comで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。