PostgresでJsonを操作

PostgresでJsonを操作
Twitter LINEで送る Facebook はてなブログ

Postgresを利用してJSONを操作する。

環境バージョン
OSmac OS 10.15.3
DBPostgreSQL 10.5

2020/02/28現在の情報をもとに作っています。

テーブル準備

CREATE TABLE users (
    id SERIAL,
    name VARCHAR(256),
    profile JSONB,
    PRIMARY KEY(id)
);

CREATE TABLE hoges (
    id SERIAL,
    user_id integer,
    value JSONB,
    PRIMARY KEY(id)
);

INSERT INTO users(name, profile) VALUES('test 1', '{"pref": "東京都","age": "30"}');
INSERT INTO users(name, profile) VALUES('test 2', '{"pref": "大阪府","age": "25"}');
INSERT INTO users(name, profile) VALUES('test 3', '{"pref": "東京都","age": "30"}');

INSERT INTO hoges(user_id, value) VALUES('1', '{"food": {"like":["肉", "魚", "野菜"]}}');
INSERT INTO hoges(user_id, value) VALUES('1', '{"food": {"unlike":["牛乳"]}}');
INSERT INTO hoges(user_id, value) VALUES('2', '{"food": {"like":["肉", "牛乳"]}}');
INSERT INTO hoges(user_id, value) VALUES('2', '{"food": {"unlike":["魚", "野菜"]}}');
INSERT INTO hoges(user_id, value) VALUES('3', '{"food": {"like":["肉", "魚", "野菜"]}}');
INSERT INTO hoges(user_id, value) VALUES('3', '{"food": {"unlike":null}}');

サンプル一覧

自分が便利と思えるSQLを記載。 もしかするともっと良いやり方はあるかもしれないが今はこれで行っています。

サンプル1

都道府県のユーザ数をカウントする.

抽出結果

   pref   | count 
----------+-------
 "東京都" |     2
 "大阪府" |     1
(2 rows)

SQL1. 無難なSQL

SELECT profile->'pref' AS pref, COUNT(profile->'pref') AS count FROM users GROUP BY profile->'pref';

SQL2. 少しJSONをベースとしたSQL

SELECT key, COUNT(value) from users, json_each_text(users.profile::json) WHERE key = 'pref' GROUP BY key;

SQL2. usersと子テーブルを件数カウント

 id |  name  | key  | sum 
----+--------+------+-----
  1 | test 1 | like |   3
  2 | test 2 | like |   2
  3 | test 3 | like |   3
(3 rows)

SELECT users.id, name, key, sum FROM users INNER JOIN (SELECT user_id, T1.key, SUM(json_array_length(T1.value::json)) from hoges, json_each_text(hoges.value::json->'food') AS T1 WHERE key = 'like' GROUP BY user_id, T1.key) AS S1 ON users.id = S1.user_id;

子テーブルの検索&groupbyをするようなクエリー Laravelなどのプログラムから呼び出すとこの様に書いた方がクエリービルダーを活用しやすい