[컴][DB] Postgresql json 관련 operator

postgresql json 관련 연산 / json 작업 / json 을 record 로 / json 을 rdbms / json 을 table 로 converting / 컨버팅

Postgresql json 관련 operator

여기서는 모든 operator 를 다루지는 않는다.

모든 operator 에 대한 이야기는 ref. 1을 확인하자.




이 글 예제에서는 다음 값을 가진 table 에 대한 query 이다.

my_table

-----------------------------------------------------------------------------------+
| name     |                data                                                   |
-----------------------------------------------------------------------------------+
| 'test'   | '{"datakey":{"10001":{"mine":"full", "tags":["a", "b"]},                                 |
|          |    "10002":{"mine":"full2", "tags":["a", "c"]}}} ...'                                    |

jsonb_each / json_each

key / value 항목들을 전부 record 로 만들어 준다. 아래 예시를 보자.

my_table 의 datakey 의 내용을 key/value 의 record 로 보여줘

SELECT json_data.key, json_data.value FROM my_table AS t, jsonb_each(t.data->'datakey') AS json_data

| key(text) | value(jsonb)     |
+------------------------------+
| 100001    | {"mine":"full"}  |
| 100002    | {"mine":"full2"} |

->

nested key 를 traverse 할때는 아래 처럼 하면 된다.

my_table 에서 root > datakey > 10001 의 값을 줘라

SELECT data->'datakey'->'10001' my_table AS a

#>

원하는 key 에 대한 값을 가져올 수 있다.


{datakey, 10001} 순서의 key(datakey의 child 로 10001 이라는 key) 에 해당하는 값을 가져올때

>> SELECT a.data #> '{datakey, 10001}' FROM my_table AS a
{"mine":"full"}

@>
특정 모양의 값을 가지고 있는지 여부, A@>B 라면, A 가 B 의 모양을 갖고 있는가.


data 의 json 이 {"datakey" :  {"10001" : {...}}} 의 모양을 갖는 object 를 가진 모든 record 를 가져와라.

SELECT * FROM my_table AS a WHERE a.data @> '{"datakey": {"10001" : {}}}'

Materialized View

기본적으로 depth 가 많은 json (nested json)은 query 를 만들기에 좋지 않아 보인다. 대략 depth 1 정도의 view 로 만들어서 원하는 query 를 만드는 것이 좋아보인다. (보충 자료 : see also. 1)

CREATE MATERIALIZED VIEW mview_table_lastest AS

  SELECT json_data.key::int AS id, json_data.value AS data
  FROM my_table AS t, jsonb_each(t.data->'datakey') AS json_data

 -- index
CREATE INDEX ON mview_table_lastest(key);

tags 'a' 를 가진 모든 data 를 보여줘라.

SELECT t.id, t.data FROM public.mview_table_lastest as t
WHERE t.data->'tags' ?| Array['a']::text[]

See Also

1. Converting JSON to PostgreSQL values, simply | End Point Blog : nested json 을 사용하기 편하게 하기 위해 Create View 를 한다.

References

1. PostgreSQL: Documentation: 9.6: JSON Functions and Operators
2. PostgreSQL: Documentation: 9.6: JSON Types
3. Querying JSON in Postgres - Schinckel.net