PostgreSQL 使用json型態欄位儲存JSON資料

PostgreSQL的json型態欄位可用來儲存JSON資料。

JSON資料是指符合RFC7159的資料。

下面建立一個employee資料表，欄位contact的型態為json。

CREATE TABLE IF NOT EXISTS employee (
   id bigserial PRIMARY KEY,
   name varchar(60) UNIQUE NOT NULL,
   age integer,
   contact json,
   create_at timestamp NOT NULL
);

新增一筆資料如下。

INSERT INTO "employee" ("name", "age", "contact", "created_at") VALUES ('john', 33, '{ "name": "mary", "phone": "0912345678" }',now());

查詢結果。

postgres=> select * from employee;
 id | name | age |                  contact                  |         created_at
----+------+-----+-------------------------------------------+----------------------------
  1 | john |  33 | { "name": "mary", "phone": "0912345678" } | 2022-02-15 21:12:26.308245
(1 row)

JSON資料其實也可用text儲存，而使用json的好處是儲存的資料必須為JSON格式，否則會報錯。例如下面新增的contact資料缺少最後的大括弧則不滿足JSON格式因此報錯。

postgres=> INSERT INTO "employee" ("name", "age", "contact", "created_at") VALUES ('tony', 33, '{ "name": "bill", "phone": "0921345678" ',now());
ERROR:  invalid input syntax for type json
LINE 1: ...ge", "contact", "created_at") VALUES ('tony', 33, '{ "name":...
                                                             ^
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: { "name": "bill", "phone": "0921345678"

JSON資料也可以jsonb以binary的方式儲存。參考「PostgreSQL json 與 jsonb 資料型態差別」。

• PostgreSQL JSON ->操作符