mysqlがjsonデータグループを解析するときにデータグループのすべてのフィールドを取得する方法

はじめに

開発プロセス中に、json データ グループの文字列に遭遇し、json グループを解析してグループ内のすべての情報を取得する必要がありました。 。次の形式:

[{"itemId":3101,"itemName":"空滤器及进气管道"},{"itemId":3102,"itemName":"水管、水泵"},{"itemId":3103,"itemName":"柴油管道"},{"itemId":3104,"itemName":"高压泵、机油泵"}]

json グループを観察すると、それが複数の json 文字列を含むリストであることがわかります。必要なのは、リスト内のすべての json 文字列を分割し、各 json 文字列を解析することです。

解析の結果、JSON文字列が単独で存在する場合はJSON_EXTRACTメソッドで抽出できることが分かりました。リストには複数の JSON 文字列が含まれているため、リストを複数の JSON 文字列に分割する必要があります。

この記事の内容を学ぶ前に、mysql の 2 つの関数を事前に理解する必要があります:

SUBSTRING_INDEX

JSON_EXTRACT

ステップ 1: 1 行を複数行に分割

1 行を複数行に分割します。つまり、リストを複数の json 行に分割します。 need

1.1 新しいテーブル keyid を作成し、次のように 0 から始まる数字のみを挿入します。

他のチュートリアルでは、次のように使用することもできます。 mysql.help_topic テーブルの help_topic_id フィールド。ただし、このライブラリ テーブルを使用するには、root 権限が必要です。したがって、独自のマッチング テーブルを作成することが最も適切です。

注: id の値は、リスト内の JSON 文字列の数より小さくすることはできません。たとえば、上記のリストに 4 つの JSON 文字列がある場合、id 値は 4 より大きい必要があります。 help_topic_id の最大値は 700 です。リスト内の json 文字列の数がこの値より大きい場合、help_topic_id を使用するのは不適切です。

1.2 分割識別子の検索

いわゆる分割識別子は、この記号に基づいて一度に複数の行に分割できる記号です。以下のリストでは分割識別子が見つからないため、処理する必要があります。 ; は分割識別子と考えることができます。処理内容は以下の通りです:

{"itemId":3101,"itemName":"空滤器及进气管道"};{"itemId":3102,"itemName":"水管、水泵"};{"itemId":3103,"itemName":"柴油管道"};{"itemId":3104,"itemName":"高压泵、机油泵"}

[ と ] の前後の 2 つのリスト記号を削除し、; を分割識別子にできるように },{ を };{ に変更します。次のように

select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest

1.3 結合により複数の行を分割します。

この時点で、maptest テーブルと新しく作成した keyid テーブルを、 JOIN 操作は、ON 条件で複数行のデータと一致します。 SUBSTRING_INDEX を介して分割します。

コードは次のとおりです。

select 
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ) AS jsonarr_info,
b.id
from 
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join keyid b 
on b.id< ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

これで、json グループを複数行に分割する作業が完了しました。

2 番目のステップ: json 文字列を解析する

複数の行に分割した後、JSON_EXTRACT を通じて解析できます。効果は次のとおりです:

完成したコードは次のとおりです:

select 
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ) AS jsonarr_info,
b.id,
JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].itemId') as itemId,
replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].itemName'),'"','') as itemName
from 
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join keyid b 
on b.id< ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

もちろん、mysql の help_topic_id フィールドを使用することも可能です。 .help_topic テーブル。コードと結果は次のとおりです:

select 
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ) AS jsonarr_info,
b.help_topic_id,
JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].itemId') as itemId,
replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].itemName'),'"','') as itemName
from 
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join mysql.help_topic b 
on b.help_topic_id < ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

注: JSON_EXTRACT によって解析されるフィールドが文字列の場合、二重引用符「" が含まれます。単に置き換えるだけです。 。 それでおしまい。

以上がmysqlがjsonデータグループを解析するときにデータグループのすべてのフィールドを取得する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。