[PostgreSql] JSON type 컬럼 Query 방법

공식 문서 참조 : https://www.postgresql.org/docs/9.3/functions-json.html

 

OperatorRight Operand TypeDescriptionExample

->	int	Get JSON array element	'[1,2,3]'::json->2
->	text	Get JSON object field	'{"a":1,"b":2}'::json->'b'
->>	int	Get JSON array element as text	'[1,2,3]'::json->>2
->>	text	Get JSON object field as text	'{"a":1,"b":2}'::json->>'b'
#>	array of text	Get JSON object at specified path	'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
#>>	array of text	Get JSON object at specified path as text	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

 

<Table: example>

Id	content
1	{   "name: "my json data 1",   "size": {     "length": 100,     "width: 200,     "isBig": false   },   "array": [     { "id": 1 },     { "id": 2 },     { "id": 3 },   ] }
2	{   "name: "my json data 2",   "size": {     "length": 100,     "width: 200,     "isBig": true   },   "array": [     { "id": 1 },     { "id": 2 },     { "id": 3 },   ] }

위와 같은 테이블과 레코드가 있다고 가정할 때, json 타입의 content 컬럼에서

 

1. name property를 읽어올때

SELECT "content" ->> 'name' FROM "example"

 

2. size.length property를 읽어올때

SELECT "content" -> 'size' ->> 'length' FROM "example"

3, size.isBig 이 true인 행들을 읽어올때

SELECT * FROM "example" WHERE ("content" -> 'size' ->> 'isBig')::bool is true

4. array 배열의 object 들 중 id property가 2인 레코드를 읽어올때

SELECT * FROM "example", json_array_elements("example" -> 'array') a WHERE (a ->> 'id')::int = 2