pgsql json,jsonb

蔚落 2021-10-06 14:44 747阅读 0赞

关于pgsql 的json 和jsonb 的数据处理笔记

1.  json 和jsonb 区别  
    两者从用户操作的角度来说没有区别，区别主要是存储和读取的系统处理（预处理）和耗时方面有区别。json写入快，读取慢，jsonb写入慢，读取快。
2.  常用的操作符

操作符：

\-> // 右边传入整数（针对纯数组），获取数组的第n个元素，n从0开始算，返回值为json

示例： `select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 // 输出 {"c":"baz"}`

\-> // 右边传入键值（针对关联数组），获取数组的第n个元素，n从0开始算，返回值为json

示例： `select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->'a' // 输出 {"b":"foo"}`

\->> // 右边传入整数（针对纯数组），获取数组的第n个元素，n从0开始算，返回值为文本

示例： `select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->>2 // 输出 {"c":"baz"}`

\->> // 右边传入键值（针对关联数组），获取数组的第n个元素，n从0开始算，返回值为文本

示例：`select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->>'a' // 输出 {"b":"foo"}`

#### // 获取json子对象，传入数组，返回json ####

示例： `select '{"a": {"b":{"c": "foo"}}}'::json#> '{a,b}' // 输出 {"c": "foo"}`

#### // 获取json子对象并转换为文本，传入数组，返回文本 ####

示例： `select '{"a": {"b":{"c": "foo"}}}'::json#>> '{a,b}' // 输出 {"c": "foo"}`

1.  操作函数  
    目前pgsql版本提供了两套函数分别处理，可以通用，名称也差不多，比如 json\_each 和 jsonb\_each ， json\_array\_elements 和 jsonb\_array\_elements 。

json相关的处理函数比较多，常用的有如下三个，这三个基本够用了

json\_object\_keys // 返回json的键（多层只返回第一层），该函数不能用于纯数组.

json\_array\_elements // 提取转换纯数组元素

json\_extract\_path // 返回JSON值所指向的某个键元素（相当于 \#> 操作符），该函数不能直接操作纯数组。

需要注意的是如果你创建字段用的是json就用json相关函数，如果创建字段用的是jsonb就用jsonb相关函数。

json\_object\_keys 函数示例：

select json_object_keys (' { "goods": [ {"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"}, {"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"} ], "quantity": "10" } ')

输出：

json\_object\_keys 函数示例：

select json_object_keys (' [ {"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"}, {"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"} ], ')

输出：

ERROR: cannot call json\_object\_keys on an array // 不能用于数组

json\_array\_elements 函数 示例:

select json_array_elements (' [ {"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"}, {"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}, {"id": "111a13d3-0225-4431-b858-678c3cfea999", "weight": "3", "quantity": "11"} ] ')

我们看到json数据被分离成三条记录，这时我们就可以对其进行查询操作，

比如查询是否包含了weight=3的数据。

select * from json_array_elements (' [ {"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"}, {"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}, {"id": "111a13d3-0225-4431-b858-678c3cfea999", "weight": "3", "quantity": "11"} ] ') as jae
    where jae::jsonb->>'weight' = '3'

# 输出： #

我们看到这样就可以到对json数据内部进行查询了。

json\_extract\_path 函数示例：

比如要获取键 ‘goods’ 的值：

{
    "goods":
    [ { "id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"}, { "id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"} ], 
    "quantity": {
       "max": "150", "min": "2"}
    }
    
    select json_extract_path   (' { "goods": [ {"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"}, {"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"} ], "quantity": {"max": "150", "min": "2"} } ' , 'goods' )  ;   // 第二个参数表示获取键为goods的值

# 输出： #

json\_extract\_path 函数和 pgsql 提供的操作符 \#> 是一样的。

select ('
    {
    "goods":
    [ 
    {
       "id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
    {
       "id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
    ], 
    "quantity": {
       "max": "150", "min": "2"}
    }
    ') ::json #> '{goods}'

两者的输出是一致的。

同样我们要输出 键quantity 下键max 的值：

select json_extract_path   ('
    {
    "goods":
    [ 
    {
       "id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
    {
       "id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
    ], 
    "quantity": {
       "max": "150", "min": "2"}
    }
    ' , 'quantity','max' ) ; 
    
    -- 或
    
    select ('
    {
    "goods":
    [ 
    {
       "id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
    {
       "id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
    ], 
    "quantity": {
       "max": "150", "min": "2"}
    }
    ') ::json #> '{quantity, max}

两者输出是一样的。

这几个函数是可以联合使用的。

比如我们要查询 键“goods” 下weight =2 的id 和quantity 值，语句如下：

select jae::json->>'id' as id, jae::json->>'quantity' as quantity from json_array_elements (
    json_extract_path (' { "goods": [ {"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"}, {"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"} ], "quantity": {"max": "150", "min": "2"} } ' , 'goods' ) ) as jae where jae::json->> 'weight' = '2'

# 输出： #

上述的json语句我们可以当做字段来使用，就相当于对表记录进行操作了。

接下来我们同个一个例子讲解json在表中的用法：

示例：查询表中jsonb\_msg字段中goods下id=1003和1002的记录，表中输入如下图：

sql查询语句：

select name,* from upgrade_test.test1 test where ( select count(*) from jsonb_array_elements ( jsonb_extract_path(test.json_msg , 'goods' ) ) as jae where jae::json->> 'id' in ('1001','1003') ) > 0 ;

# 输出： #

效率还行：

Total query runtime: 11 msec  
检索到 2 行。

官方文档页：

[https://www.postgresql.org/docs/9.4/static/functions-json.html][https_www.postgresql.org_docs_9.4_static_functions-json.html]

--------------------

PostgreSQL9.4 新增 JSONB 数据类型， JSONB 同时属于 JSON (JavaScript Object Notation) 数据类型，jsonb 和 json 的输入数据几乎完全通用，最大的差别体现在效率上，json 存储的数据几乎和输入数据一样，存储的是未解析的数据，调用函数时使用效率较低; 而 jsonb 存储的是分解的 binary 格式数据，使用时不需要再解析了，因此使用上效率较高; 另一方面 json 在写入时较快，而 jsonb 写入时由于需要转换导致写入较慢。下面通过些简单的例子了解两者的差异。

–1 这个例子两者没啥差异  
francs=> `SELECT '[1, 2, "foo", null]'::json;`

## json ##

\[1, 2, “foo”, null\]  
(1 row)

francs=> `SELECT '[1, 2, "foo", null]'::jsonb;`

## jsonb ##

\[1, 2, “foo”, null\]  
(1 row)  
备注: json 类型输出的内容和写入的内容一样，不会对输出的结果改变，而 jsonb不一样，看下面的例子。

–2 jsonb 输出内容顺序不一样  
francs=> `SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;`

## json ##

\{“bar”: “baz”, “balance”: 7.77, “active”:false\}  
(1 row)

francs=> `SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;`

## jsonb ##

\{“bar”: “baz”, “active”: false, “balance”: 7.77\}  
(1 row)

–3 jsonb: 整数类型输出不一样  
francs=> `SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;`  
json | jsonb  
———————–+————————-  
\{“reading”: 1.230e-5\} | \{“reading”: 0.00001230\}  
(1 row)

–4 jsonb: 去掉了空格  
francs=> `select ' {"id":1, "name":"francs", "remark":"a good guy!" }'::json;`

## json ##

\{“id”:1, +  
“name”:”francs”, +  
“remark”:”a good guy!”+  
\}  
(1 row)

francs=> `select ' {"id":1, "name":"francs", "remark":"a good guy!" }'::jsonb;`

## jsonb ##

\{“id”: 1, “name”: “francs”, “remark”: “a good guy!”\}  
(1 row)

–5 jsonb: 重复的元素值仅保留最后一个  
francs=> `select ' {"id":1, "name":"francs", "remark":"a good guy!", "name":"test" }'::jsonb;`

## jsonb ##

\{“id”: 1, “name”: “test”, “remark”: “a good guy!”\}  
(1 row)  
备注： json 类型的输出和输入一样，会保留所有重复的元素，而 jsonb 对于重复的元素仅保留最后出现的重复元素。

–6 关于索引

GIN 索引支持 jsonb 类型，支持大的 jsonb 表中基于 keys 或者 key/values 模式的检索。  
默认的 GIN 索引模式支持带有 @>, ?, ?& 和 ?| 操作的查询，关于这些操作符的含义参考本文的附录。

假如有一个文档：

{
        "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
        "name": "Angela Barton",
        "is_active": true,
        "company": "Magnafone",
        "address": "178 Howard Place, Gulf, Washington, 702",
        "registered": "2009-11-07T08:53:22 +08:00",
        "latitude": 19.793713,
        "longitude": 86.513373,
        "tags": [ "enim", "aliquip", "qui" ] }

我们将表名定义为 api, jsonb 字段为 jdoc，创建如下索引

CREATE INDEX idx_gin_api_jdoc ON api USING gin (jdoc);

那么如下的查询可以使用索引

-- Find documents in which the key "company" has value "Magnafone"
    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

备注：上面这个例子来自手册。

7 附 Additional jsonb Operators

--------------------

<table> 
 <thead> 
  <tr> 
   <th>Operator</th> 
   <th>Right Operand Type</th> 
   <th>Description</th> 
   <th>Example</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>jsonb</td> 
   <td>Are the two JSON values equal?</td> 
   <td>‘[1,2,3]’::jsonb = ‘[1,2,3]’::jsonb</td> 
  </tr> 
  <tr> 
   <td>@&gt;</td> 
   <td>jsonb</td> 
   <td>Does the left JSON value contain within it the right value?</td> 
   <td>‘{“a”:1, “b”:2}’::jsonb @&gt; ‘{“b”:2}’::jsonb</td> 
  </tr> 
  <tr> 
   <td>&lt;@</td> 
   <td>jsonb</td> 
   <td>Is the left JSON value contained within the right value?</td> 
   <td>‘{“b”:2}’::jsonb &lt;@ ‘{“a”:1, “b”:2}’::jsonb</td> 
  </tr> 
  <tr> 
   <td>?</td> 
   <td>text</td> 
   <td>Does the key/element string exist within the JSON value?</td> 
   <td>‘{“a”:1, “b”:2}’::jsonb ? ‘b’</td> 
  </tr> 
  <tr> 
   <td>?</td> 
   <td></td> 
   <td>text[]</td> 
   <td>Do any of these key/element strings exist?</td> 
  </tr> 
  <tr> 
   <td>?&amp;</td> 
   <td>text[]</td> 
   <td>Do all of these key/element strings exist?</td> 
   <td>‘[“a”, “b”]’::jsonb ?&amp; array[‘a’, ‘b’]</td> 
  </tr> 
 </tbody> 
</table>

--------------------

8 参考  
WAITING FOR 9.4 – INTRODUCE JSONB, A STRUCTURED FORMAT FOR STORING JSON  
PostgreSQL 9.4 Beta 1发布，支持JSONB  
PostgreSQL 9.4 new data type jsonb - do not need to reparser when used  
JSON Types

[https_www.postgresql.org_docs_9.4_static_functions-json.html]: https://www.postgresql.org/docs/9.4/static/functions-json.html