JSONオブジェクトと第一正規形【その２】
asai![DSC_1228[1].jpg](https://asai-atsushi.up.seesaa.net/_imagesblog_f55asai-atsushi/DSC_12285B15D.jpg)
前回に続きRDBMSにおけるJSON型の話である。
前回では、JSON型の列に格納されているJSONデータから「一部を取り出す方法」についてみてきた。演算子->>を使う方法、JSONパスといくつか方法がある。
今回は、JSONデータのうちJSON配列をRDBMSのテーブルのように変換する方法について解説したいと思う。
JSON配列のテーブル化
JSON配列をテーブルのように扱うことができれば、SQL命令でJSONデータを操作できるので便利だと容易に想像できる。
例えば、JSON配列に格納されているJSONオブジェクトのうち「idフィールドが1であるオブジェクトを検索する」といった操作を、使い慣れているSELECT命令で書けると便利でしょ？
これを実現するためにはJSONデータをテーブルに置き換えるという処理が必要なのだが、データベース毎に事情があるようなので、個別に見ていくことにしよう。
まずは、PostgreSQLである。ポスグレではjsonb_to_recordsetでJSONデータをテーブルのように変換することができる。
select * from jsonb_to_recordset('[{"name": "asai","age": null},{"name": "taro","age": 30},{"name": null,"age": 40}]'::jsonb)
as json_data("name" text, "age" integer)
name age
------- ------
asai
taro 30
40
JSON配列の要素がレコードとなり、配列要素であるJSONオブジェクトのフィールドが列になって結果として得られる。
jsonb_to_recordsetでは別名指定のASで列名と型を指定する必要がある。
得られる結果はテーブルではなくレコードセットであるため参照のみが可能。残念ながら更新系の命令では使用できない。SELECT命令でのみ使用できるが、FROM句で使用するのが定石。
別名で付けた列名はSELECT句、WHERE句で使用することができる。
select name from jsonb_to_recordset('[{"name": "asai","age": null},{"name": "taro","age": 30},{"name": null,"age": 40}]'::jsonb)
as json_data("name" text, "age" integer)
where name is not null and age is not null
name
-------
taro
次にOracleである。オラクルさんではjson_tableという関数でJSONデータをテーブル化できる。
select * from json_table('[{"name": "asai","age": null},{"name": "taro","age": 30},{"name": null,"age": 40}]',
'$[*]' columns(name varchar2(8) path '$[*].name', age number path '$[*].age'))
as json_data
name age
------- ------
asai
taro 30
40
ポスグレのjsonb_to_recordsetと同じ感じだけど、JSONパスで列を指定しないとうまくいかない。ネストが深くなると大変。
別名で付けた列名はSELECT句、WHERE句で使用することができる。
select name from json_table('[{"name": "asai","age": null},{"name": "taro","age": 30},{"name": null,"age": 40}]',
'$[*]' columns(name varchar2(8) path '$[*].name', age number path '$[*].age'))
as json_data where name is not null and age is not null
name
-------
taro
さて、JSONデータをポスグレのjsonb_to_recordset()や、オラクルのjson_table()でテーブルのように変換してSELECT命令で使えることを見てきたわけではあるが、実際のJSONデータはテーブルのJSON列に格納されていることが多いであろう。
まぁちょっとサンプルのデータを作ってみる。ますはポスグレ。
create table jfoo (
a integer not null,
j jsonb
);
insert into jfoo values(1,'[
{"name":"asai","age":58},
{"name":"suzuki","age":32},
{"name":"honda","age":45}]');
insert into jfoo values(2,'[
{"name":"ichiro","age":52},
{"name":"syouhei","age":25}]');
insert into jfoo values(3,'[
{"name":"honda","age":45}]');
とりあえずできたので確認してみよう。
postgres=# select * from jfoo;
a | j
---+--------------------------------------------------------------------------------------------
1 | [{"age": 58, "name": "asai"}, {"age": 32, "name": "suzuki"}, {"age": 45, "name": "honda"}]
2 | [{"age": 52, "name": "ichiro"}, {"age": 25, "name": "syouhei"}]
3 | [{"age": 45, "name": "honda"}]
ふむふむ、なかなかいい感じではないかな。
一応説明するとa列はレコード番号。連番で1、2、3と入れてみた。
j列がJSONデータで内容はnameとageフィールドを持つオブジェクトの配列が格納されている。
JSON型が無かった頃のRDBMSだとnameとage列を持つ別テーブルにして結合して使うような感じかな。
JSON型を導入することで、テーブル分割しなくて済むし、結合もしなくて良いので楽チンなんだけどJSON配列の要素を別々に見たい場合にはやはり結合する必要がある。
postgres=# select a, name, age from jfoo inner join jsonb_to_recordset(j) as (name varchar(10), age integer) on true; a | name | age ---+---------+----- 1 | asai | 58 1 | suzuki | 32 1 | honda | 45 2 | ichiro | 52 2 | syouhei | 25 3 | honda | 45
自己結合なのでon以下の結合条件が「trueだけ」でなんか気持ち悪いけど、まぁこんな感じになる。inner joinじゃなくてcross joinにすればonの条件式は書かなくてOK。
j列がnullの場合もあり、そのレコードも含めるのなら、left joinを使えばよい。
postgres=# select a, name, age from jfoo left join jsonb_to_recordset(j) as (name varchar(10), age integer) on true; a | name | age ---+---------+----- 1 | asai | 58 1 | suzuki | 32 1 | honda | 45 2 | ichiro | 52 2 | syouhei | 25 3 | honda | 45 4 | |
テーブルがひとつだけなのに結合しなくちゃいけなくて、しかも自己結合だからなんかよくわからん状態。これに他のテーブルとさらに結合するとなると、またちょっとややこしくなる。
試しにやってみよう。テーブルjbarを作って結合させてみる。
create table jbar ( a integer not null, b varchar(32) not null ); insert into jbar values(1,'no name'); insert into jbar values(2,'asai'); insert into jbar values(3,'suzuki'); insert into jbar values(4,'honda');
b列にnameフィールドの内容を入れてみた。これで結合させたい。
postgres=# select jfoo.a, j.name, j.age, jbar.a from jfoo cross join jsonb_to_recordset(j) as j(name varchar(10), age integer) left join jbar on j.name = jbar.b; a | name | age | a ---+---------+-----+--- 2 | syouhei | 25 | 1 | suzuki | 32 | 3 1 | asai | 58 | 2 3 | honda | 45 | 4 1 | honda | 45 | 4 2 | ichiro | 52 |
なんかうまくいったぽいけど...ややこしい。
NESTED
Oracleでも同じようにできる。jsonb_to_recordset()ではなくjson_table()を使えば良い。
SQL> select jfoo.a, j.name, j.age, jbar.a from jfoo cross join json_table(j, '$[*]' columns (name varchar2(10), age number)) as j left join jbar on j.name = jbar.b; A NAME AGE A ---------- ---------- ---------- ---------- 1 asai 58 2 1 suzuki 32 3 1 honda 45 4 2 ichiro 52 2 syouhei 25 3 honda 45 4
json_tableの場合は第二引数にJSONパス式を書く必要がある。jsonb_to_recordsetでは別名で列定義していたが、json_tableでは専用のcolumnsで列定義する。細かい違いだが注意しないとハマりそう。
Oracleにはうれしいことに、シンタックスシュガーが用意されている。それは何かというと「NESTED」である。
NESTEDの本来の目的は、JSONデータの中にいくつも配列がネストして存在している場合でも対応できるように考えられた「もの」らしいが、JOINをすっきり書けるので「おすすめ」である。
SQL> select jfoo.a, name, age, jbar.a from jfoo nested j[*] columns (name varchar2(10), age number) left join jbar on name = jbar.b; A NAME AGE A ---------- ---------- ---------- ---------- 1 asai 58 2 1 suzuki 32 3 1 honda 45 4 2 ichiro 52 2 syouhei 25 3 honda 45 4
普通の結合ではないみたいなので、ASで別名を付けることができなかった。別名を付けたい場合はjson_tableを使えということなのかな。
MS SQL ServerのOPENJSON
ついでにSQL ServerでのJSON配列のテーブル化について調査したので載せておく。
SQL ServerではOPENJSONでJSONデータをテーブル化することができる。JSONが後に付いているのでなんだか仲間外れな気がするがそういうものなのでしょうがないとする。
select jfoo.a, oj.name, oj.age from jfoo cross apply openjson(j) with(name nvarchar(32), age integer) as oj; a | name | age ---+---------+----- 1 | asai | 58 1 | suzuki | 32 1 | honda | 45 2 | ichiro | 52 2 | syouhei | 25 3 | honda | 45 4 | |
使い方はjson_tableやjson_to_recordsetと同じようにする。列定義はwithで行う。
SQL Serverではcross applyを使って結合できる。
UPDATEできるか
JSON配列をテーブル化したものに対してUPDATEやINSERTが使えたら便利なんじゃない？と思ったりする。例えば以下のような感じ
update jsonb_to_recordset('[{"name": "asai","age": null},{"name": "taro","age": 30},{"name": null,"age": 40}]'::jsonb)
as json_data("name" text, "age" integer)
set name = name || '様'
where name is not null
残念ながら、このクエリはエラーになり実行できない。
jsonb_to_recordsetが戻すデータはあくまでもレコードセットなのでテーブルとはちょっと違うものになっている。
クエリの実行結果をクライアント側に持ってきたデータである「ミドルウェアでのレコードセット」と似ているかも知れない。レコードセットに対して仮に書き換えが可能だとしてもそれはクライアント側での話で、サーバに反映させるには別の手段が必要。
JSONデータの一部を書き換えたり削除するには一度JavaScript環境に持ってきてオブジェクトを編集してJSONデータを書き戻す、というのが手っ取り早い。SQLだけでやるのはちょっとしんどいかも。
OracleやMySQLにはjson_transformという関数が用意されていて、JSONデータを操作できるようなのだが、これについては勉強中なので少々お待ちを。次回は解説したいけど、どうかな？
SQLやJSONとは無関係なのだが、新しく本が出るのでその宣伝をしておく。いわゆる案件である。
なんのことはない、Visual Studioが新しくなったので改訂版が出ますということだけでした。
みなさん宜しくお願い致します。
投稿者プロフィール
最新の投稿
コンピューター関係の記事2025年4月26日アイキャッチ画像にSQLアイコンとJSONアイコンが欲しい
コンピューター関係の記事2025年4月23日AdSense審査に無事パス- コンピューター関係の記事2025年4月22日SSブログ復活
- コンピューター関係の記事2025年4月22日SSブログが無くなった余波は大きい
