postgresql
JSON 지원

소개

순수한 JSON 테이블 만들기

순수한 JSON 테이블을 만들려면 JSONB 유형의 단일 필드를 제공해야합니다.

CREATE TABLE mytable (data JSONB NOT NULL);

기본 색인도 만들어야합니다.

CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);

이 시점에서 테이블에 데이터를 삽입하고 효율적으로 쿼리 할 수 ​​있습니다.

복잡한 JSON 문서 쿼리하기

테이블에서 복잡한 JSON 문서 가져 오기 :

CREATE TABLE mytable (data JSONB NOT NULL);
CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);
INSERT INTO mytable VALUES($$
{
    "name": "Alice",
    "emails": [
        "[email protected]",
        "[email protected]"
    ],
    "events": [
        {
            "type": "birthday",
            "date": "1970-01-01"
        },
        {
            "type": "anniversary",
            "date": "2001-05-05"
        }
    ],
    "locations": {
        "home": {
            "city": "London",
            "country": "United Kingdom"
        },
        "work": {
            "city": "Edinburgh",
            "country": "United Kingdom"
        }
    }
}
$$);

최상위 요소 쿼리 :

SELECT data->>'name' FROM mytable WHERE data @> '{"name":"Alice"}';

배열의 간단한 항목 쿼리 :

SELECT data->>'name' FROM mytable WHERE data @> '{"emails":["[email protected]"]}';

배열의 객체 쿼리 :

SELECT data->>'name' FROM mytable WHERE data @> '{"events":[{"type":"anniversary"}]}';

중첩 된 객체 쿼리 :

SELECT data->>'name' FROM mytable WHERE data @> '{"locations":{"home":{"city":"London"}}}';

-> 및 ->> 과 비교하여 @> 실적

쿼리의 WHERE 부분에서 @> , -> 및 ->> 을 사용하는 경우의 성능 차이를 이해하는 것이 중요합니다. 이 두 검색어는 대체로 동일하지만

SELECT data FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data FROM mytable WHERE data->'name' = '"Alice"';
SELECT data FROM mytable WHERE data->>'name' = 'Alice';

첫 번째 명령문은 위에 작성된 색인을 사용하지만 후자의 명령문은 완전한 표 스캔을 요구하지 않습니다.

결과 데이터를 얻을 때 여전히 -> 연산자를 사용할 수 있으므로 다음 쿼리에서도 인덱스를 사용합니다.

SELECT data->'locations'->'work' FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data->'locations'->'work'->>'city' FROM mytable WHERE data @> '{"name":"Alice"}';

JSONb 연산자 사용

DB와 테이블 만들기

DROP DATABASE IF EXISTS books_db;
CREATE DATABASE books_db WITH ENCODING='UTF8' TEMPLATE template0;

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

DB 채우기

INSERT INTO books(client, data) values (
    'Joe', 
    '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
),(
    'Jenny', 
    '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
),(
    'Jenny', 
    '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
);

테이블 책 안의 모든 것을 보게하십시오 :

SELECT * FROM books;

산출:

-> 연산자는 JSON 열에서 값을 반환합니다.

1 열 선택 :

SELECT client, 
    data->'title' AS title
    FROM books;

산출:

2 열 선택 :

SELECT client, 
   data->'title' AS title, data->'author' AS author
   FROM books;

산출:

-> 대 ->>

-> 연산자는 원본 JSON 유형 (객체 일 수 있음)을 반환하지만 ->> 는 텍스트를 반환합니다.

NESTED 객체 반환

-> 를 사용하여 중첩 된 객체를 반환하고 연산자를 연결할 수 있습니다.

SELECT client, 
   data->'author'->'last_name' AS author
   FROM books;

산출:

필터링

JSON 내부의 값을 기반으로 행을 선택하십시오.

 SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'title' = '"Dharma Bums"';

JSON '"Dharma Bums"' 비교해야하는 곳에 -> 사용하는 것에 '"Dharma Bums"'

또는 ->> 을 사용하고 'Dharma Bums' 와 비교할 수 있습니다.

산출:

중첩 필터링

중첩 된 JSON 객체의 값을 기반으로 행 찾기 :

SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'author'->>'last_name' = 'Kerouac';

산출:

현실 세계의 예

CREATE TABLE events (
  name varchar(200),
  visitor_id varchar(200),
  properties json,
  browser json
);

이 표에는 페이지 뷰와 같은 이벤트가 저장됩니다. 각 이벤트는 속성 (예 : 현재 페이지)과 브라우저 (예 : OS, 화면 해상도 등)에 대한 정보를 전송합니다. 이 둘은 완전히 자유로운 형태이며 시간이 지남에 따라 바뀔 수 있습니다 (추적 할 여분의 물건을 생각할 때).

INSERT INTO events (name, visitor_id, properties, browser) VALUES
(
  'pageview', '1',
  '{ "page": "/" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'pageview', '2',
  '{ "page": "/" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1920, "y": 1200 } }'
),(
  'pageview', '1',
  '{ "page": "/account" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'purchase', '5',
  '{ "amount": 10 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1024, "y": 768 } }'
),(
  'purchase', '15',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),(
  'purchase', '15',
  '{ "amount": 500 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
);

이제 모든 항목을 선택할 수 있습니다.

SELECT * FROM events;

산출:

JSON 연산자 + PostgreSQL 집계 함수

전통적인 PostgreSQL 집계 함수와 결합 된 JSON 연산자를 사용하여 우리가 원하는 것을 추출 할 수 있습니다. 당신은 당신의 처분으로 RDBMS를 충분히 활용할 수 있습니다.

• 브라우저 사용법을 보게하십시오 :

    SELECT browser->>'name' AS browser, 
      count(browser)
      FROM events
      GROUP BY browser->>'name';
  

산출:

• 방문자 당 총 수익 :

    SELECT visitor_id, SUM(CAST(properties->>'amount' AS integer)) AS total
    FROM events
    WHERE CAST(properties->>'amount' AS integer) > 0
    GROUP BY visitor_id;
  

산출:

• 평균 화면 해상도

    SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width,
      AVG(CAST(browser->'resolution'->>'y' AS integer)) AS height
    FROM events;
  

산출:

여기 와 여기에 더 많은 예제와 문서가 있습니다 .