JSONオブジェクトと第一正規形【その２】

前回に続き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とは無関係なのだが、新しく本が出るのでその宣伝をしておく。いわゆる案件である。

3ステップでしっかり学ぶ　Visual Basic入門　改訂第3版

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

なんのことはない、Visual Studioが新しくなったので改訂版が出ますということだけでした。
みなさん宜しくお願い致します。

---

サイト内を検索