はじめに
これはPostgreSQLを0から学ぶための入門記事ではありません。
誤解を招くタイトルで申し訳ありません。
https://qiita.com/noborus/items/5e39d144b1510f6865bd
とてもおもしろい記事でしたので、勝手にちょっと名前をもらってきました。
PostgreSQLの配列とJSON
PostgreSQLには配列型というものがあります。
基本正規化されているDBのテーブルではそう見かけることはないですが、パフォーマンスのため正規化を崩すときや、ちょっと凝ったクエリをSQLで書くときには役に立つ、なかなかに渋い奴です。
似たよう使われ方をするものにJSON型、JSONB型というものもあります。こちらは先ほどと似たような使い方をするほか、APIのレスポンスデータのような最初からJSONとして取り扱いたいデータを扱う場合に効果的です。
JSONBは表現力が高く、いろいろなJSONBを作ることができます。
以下のクエリでできるものは全部JSONBです。
-- {}
select '{}'::JSONB;
jsonb
------
{}
(1 row)
-- 1
select '1'::JSONB;
jsonb
-------
1
(1 row)
select '[{"value":0}]'::JSONB;
jsonb
-------
[{"value": 0}]
select NULL::JSONB;
jsonb
-------
[NULL]
本題
以下の2つのクエリを実行したとき、返ってくる値はそれぞれなんでしょうか？
-- クエリ1
select '[{"value":0},{"value":1},{"value":2}]'::JSONB->0;
-- クエリ2
select (array[0,1,2])[0];
答えは、1つめが{"value":0}で、2つ目がNULLです。
select '[{"value":0},{"value":1},{"value":2}]'::JSONB->0;
jsonb
-------
{"value": 0}
select (array[0,1,2])[0];
jsonb
-------
[NULL]
同じPostgreSQLの中でも、配列は1始まりで、JSONの配列は0始まりなのでした。
冷静に考えると1つの言語の中でこの両方があるのはなかなかすごい言語な気がします。
ただJavaScript Object Notationに1-indexedでアクセスするのもやりたくないですし、別におかしいことはないです。ところでPostgreSQLってJSONB配列の配列も書けますよね？
select ((array['[{"value":0}]'::JSONB,'[{"value":1}]'::JSONB,'[{"value":2}]'::JSONB])[1])->0;
jsonb
-------
{"value": 0}
さすがに苦しい！
まとめ
SELECT FROM オチ;
--
(1 rows)
おまけ
-- JSONパスでも0始まり
select * from jsonb_path_query('[{"value":0},{"value":1},{"value":2}]'::JSONB, '$[0]');
jsonb
-------
{"value": 0}