JSONオブジェクトと第一正規形：AABlog：So-netブログ

	ブログをはじめるログイン

TP-Link 10G 5ポート L2S.. ｜JSONオブジェクトと第一正規形【その２.. ブログトップ

JSONオブジェクトと第一正規形　[SQLポケリ] [編集]

本日は、RDBMSにおけるJSON型のお話である。
RDBMSって何？JSONって何？な人はまずはそれらを勉強してからでないと「まるで話がわからない」と思うので注意されたし。

多くの場合JSON型の列に入れる値はJSONオブジェクトか配列であろう。文字列や数値を格納するメリットがあまり思いつかない。JSONオブジェクトや配列をセル(フィールド)に格納した場合、第一正規形にならない。何を言っているのかさっぱりという方に向け少し説明すると、RDBMSでは「ひとつのセルにはひとつのデータを格納する」といった定石(セオリー)みたいなものがある。この定石に従っている表データが「第一正規形」と呼ばれる。

閑話休題、ここで宣伝である。正規化についてはSQLポケリには書いてない。データベース設計の話になるからね。「SQLはじめの一歩」には載っているので興味のある方はこちらを参照して欲しい。

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

作者: 朝井淳
出版社/メーカー: 技術評論社
発売日: 2015/03/03
メディア: 単行本（ソフトカバー）

JSONオブジェクトは複数のデータをまとめることができるので、セルの中に複数のデータを記録することができ、RDBMSの伝統的な掟である「第一正規形」に違反してしまうのである。JSON型は掟破りの必殺技なのである。

まぁ、第一正規形などの正規化が考えられたのは、もう何十年も前の話。かつて最強と思われた定石は今や古臭いものとなってしまったのかも知れない。

ちょっと話が逸れ始めてしまったではないか。元に戻そう。JSON型の列にはその値としてJSONオブジェクト・配列を記録することが多いのだが、そうすることで第一正規形ではなくなる。
元々SQLはレコードのフィールド(テーブルのセル)にはひとつの値しか格納しないことが前提となっている。そう、JSON型により掟破りなデータが入り込むのである。昔のSQLにはJSON型を扱うようなしくみがないことはもちろんのこと、開発スピードが速く標準化が間に合わず各RDBMSで独自仕様の拡張がなされ「方言が多い」状態になっている。

と嘆いてみてもしょうがない。各RDBMSごとにどうなっているのかを見ていくことにしよう。

PostgreSQLにおけるJSON

PostgreSQLでは割と前からJSON型に対応している気がする。普通にJSON型があるし、JSONBといったバイナリ形式のJSON型もある。JSONはJavaScript文法の一部なので、テキストデータといっても良い。JSON型が使えないRDBMSでも通常の文字列型にJSONデータを保存することが可能なのでテキストデータであることは明白であるのだが、わざわざJSON型を使う理由としては...

JSON型にすると文法チェックが入るのでデータの整合性を高めることができる。これがJSON型を使うことのメリットである。さらに、バイナリのJSON型にすると内部で扱いやすいように構造を変えて保存されるらしい。詳しい事柄はよくわからないが、内部処理が省けるのでJSONよりJSONBの方が高速、ということみたい。JSONBにはインデックスを作成することができるので、検索が速くなるらしいが、どの程度なのかはよくわからない。

さて、そんなJSON型には当然ながらJSONデータを格納することができる。前にも言及した通りRDBMSにおいて扱われるJSONデータはJSONオブジェクトかJSON配列が多いであろう。。
JSONオブジェクトとJSON配列は構造としてはどちらも同じようなもの。添え字が整数であるのが配列で、添え字が文字列なのがオブジェクトである。

JSON配列	JSONオブジェクト	JSONオブジェクト(フィールド名表記)
添え字が整数	添え字が文字列	フィールド名
var[0] = "zero"	var["zero"] = 0	var.zero = 0
var[1] = "one"	var["one"] = 1	var.one = 1

PostgreSQLにおけるJSONオブジェクトの各フィールドへのアクセスは、->を使う。
JSON型の列名に->を付け、その後に整数値か文字列を付け足すことでJSONオブジェクトの子要素を参照することができる。

select jobj, jobj->0 e0 from testjson

jobj                  e0
--------------------- -------------
["zero","one"]        "zero"
{"zero": 0,"one": 1}

jobj->0とすれば、JSON型の列jobjから配列要素の先頭(0番目)だけを取得することができる。jobjに配列ではなくJSONオブジェクトが保存されているときっとうまくいかない。NULLが戻される。

select jobj, jobj->'one' one from testjson

jobj                  one
--------------------- -------------
["zero","one"]        
{"zero": 0,"one": 1}  1

jobj->'one'とすれば、JSON型の列jobjからoneフィールドだけを取得することができる。jobjに配列が保存されているときっとうまくいかない。NULLが戻される。
JSONでの文字列は"で囲むが、SQLでは'で囲む。

->ではJSON型から抽出してJSON型で結果を戻す。これに対して、>をふたつにして->>とすることでtext型で結果を戻すようにすることができる。

select jobj, jobj->>0 e0 from testjson

jobj                  e0
--------------------- -------------
["zero","one"]        zero
{"zero": 0,"one": 1}

"zero"とzeroの違いがある。

* MySQLにおいても-> ->>を使ってフィールド/要素アクセス可能だが、$.フィールド名のようにする必要あり。微妙に異なる。
* Oracleには->は存在しない。.でフィールド指定する。型を指定する場合は.string()や.number()を付ける。
* Oracle、MySQLではJSON_VALUE関数でJSONデータからパスを使用して値を抽出することができる。

JSON_TYPEOF

JSON型にはJSONデータを格納することができるが、JSONデータには以下の種類がある。

データ型	JSON_TYPEOFの戻り値
オブジェクト	object
配列	array
文字列	string
数値	number
論理値	boolean

フィールドにどういった種類のJSONデータが格納されているのかについてはJSON_TYPEOF関数を使って調べることができる。

select jobj, json_typeof(jobj) json_type from testjson

jobj                  json_typeof
--------------------- -------------
["zero","one"]        array
{"zero": 0,"one": 1}  object

* MySQLではJSON_TYPEを使用する。

オブジェクトのネスト

JSONではデータをネストさせることができる。配列の各要素がオブジェクトであったり、その逆でオブジェクトのフィールドが配列となっていたりしてもOKなのである。

配列の各要素がオブジェクトの例

[{"name": "asai","age": null},{"name": "taro","age": 30}]

オブジェクトのフィールドが配列の例

{"one_tow_three": [1, 2, 3]}

オブジェクトのフィールドがオブジェクトの例

{"one": {"name": "asai","age": null}, "two": {"name": "taro","age": 30}}

いろいろな組み合わせが無限に考えられるが、多くても4階層くらいまでかな。ツリー構造になっていると、もっとネストが深くなるかも知れないけど。
ネストしているJSONオブジェクトにアクセスしたい場合は、->を連続して使うことも可能だが、パスで指定することも可能。パスの場合は#>を使用する。

oneフィールドの下にあるnameフィールドを->の連続で抽出

select '{"one": {"name": "asai","age": null}, "two": {"name": "taro","age": 30}}'::json->'one'->'name' one_name

one_name
---------
"asai"

oneフィールドの下にあるnameフィールドを#>とパスで抽出

select '{"one": {"name": "asai","age": null}, "two": {"name": "taro","age": 30}}'::json
 #>'{one, name}' one_name

one_name
---------
"asai"

#>はjson_extract_path関数に、#>>はjson_extract_path_text関数とそれぞれ置き換え可能。

SQL/JSONパス式

PostgreSQL12になってJSON関係の機能が追加されている。JSONパス式もそのひとつである。パス式はSQL/JSON標準で規定されている規格となっている。#>のところで解説したパスとは別物になっているので注意されたし。

JSONパス式は$から始める。$がルートを意味する。
.に続けてフィールド名を指定することができる。
配列要素は[0]のように指定できるが、メタ文字である*を使用することも可能。[*]とすれば全要素が対象になる。

これだけ押さえておけば大体は把握できたと思っても良い？だろう。

簡単な例からやってみるか。

oneフィールドの下にあるnameフィールドをJSONパスで抽出

select jsonb_path_query('{"one": {"name": "asai","age": null}, "two": {"name": "taro","age": 30}}'::jsonb,
  '$.one.name'::jsonpath) one_name

one_name
---------
"asai"

jsonb_path_queryはJSONB型のデータからJSONパスに合致するJSONデータを戻す関数。
JSONパスは「$.one.name」となっている。これで、oneフィールドの下にあるnameフィールドが抽出できる。

*Oracle23cではjson_query関数を使用する。

select json_query('{"one": {"name": "asai","age": null}, "two": {"name": "taro","age": 30}}',
  '$.one.name') one_name

one_name
---------
"asai"

続けて、配列の例を見てみよう。

配列の各要素からnameフィールドだけを抽出

select jsonb_path_query('[{"name": "asai","age": null},{"name": "taro","age": 30}]'::jsonb,
 '$[*].name') name_value

name_value
----------
"asai"
"taro"

JSONパスは「$[*].name」となっている。これで、全要素のnameフィールドが抽出できる。*が任意の添え字＝全要素を意味している。配列にはふたつの要素があるので結果は2行になる。

このようにjsonb_path_queryでは複数行が戻される場合があることに注意。jsonb_path_query_arrayを使うことで、結果を配列に集約させることができる。

配列の各要素からnameフィールドだけを配列に集約して抽出

select jsonb_path_query_array('[{"name": "asai","age": null},{"name": "taro","age": 30}]'::jsonb,
 '$[*].name') name_value

name_value
----------------
["asai", "taro"]

*Oracle23cではjson_query関数にwith array wrapperオプションを付けて使用する。

select json_query('[{"name": "asai","age": null},{"name": "taro","age": 30}]',
 '$[*].name' with array wrapper) name_value

name_value
----------------
["asai", "taro"]

うーんやっぱり方言は残ってしまうのか。

条件式

JSONパスでは条件式を付けることができる。

配列の各要素からageフィールドだけを抽出するがnullであるものは除く

select jsonb_path_query('[{"name": "asai","age": null},{"name": "taro","age": 30}]'::jsonb,
 '$[*].age ? (@ != null)') age_value

age_value
----------
30

JSONパス式に?を追加して、さらに(と)の間に条件式を書けばよい。@はパス式にマッチしたJSONオブジェクトを意味する。

*Oracle23cではjson_query関数を使用する。

select json_query('[{"name": "asai","age": null},{"name": "taro","age": 30}]',
 '$[*].age ? (@ != null)') age_value

age_value
----------
30

ageがnullでない条件はそのままで、配列要素全体を取得したい場合は以下のようにもできる。

配列の各要素を抽出するがageフィールドがnullであるものは除く

select jsonb_path_query('[{"name": "asai","age": null},{"name": "taro","age": 30}]'::jsonb,
 '$[*] ? (@.age != null)') obj_value

obj_value
----------
{"age": 30, "name": "taro"}

ANDやOR条件を記述することも可能だが、JavaScriptでの演算子記法となる。

配列の各要素を抽出するがageフィールドかnameフィールドがnullであるものは除く

select jsonb_path_query('[{"name": "asai","age": null},{"name": "taro","age": 30},{"name": null,"age": 40}]'::jsonb,
 '$[*] ? (@.age != null && @.name != null)') obj_value

obj_value
----------
{"age": 30, "name": "taro"}

話が長くなってしまったので本日は以上。
次回は、JSON配列をテーブルのように扱う方法について解説したい。

SQLやJSONとは無関係なのだが、新しく本が出るのでその宣伝をしておく。いわゆる案件である。