实例说明PostgreSQL中如何使用jsonb

来自yinmingjun，引用请注明！

 

 PostgreSQL对jsonb支持的概要说明

如何理解PostgreSQL的jsonb的支持呢？下面的案例给出了答案。

 

先从机制上说明一下，PostgreSQL中jsonb是按文本存储，在处理的时候解析，所以分存储和解析两个阶段！

SQL的输入输出的阶段，和jsonb的存储阶段等同，都是作为未解析的数据来处理。

 

对于PostgreSQL来说，在SQL层面只存在jsonb一个类型，而jsonb的内部（解析json层面），存在不同的数据类型，包括: string, number, object, array等。在SQL的输入输出层面上，只能是通过jsonb来传输，jsonb可以理解为一个字符串，其内容符合json解析规则。可以简单的将jsonb强制转换为varchar或text，来获取符合json格式的文本。

 

但是，在很多时候，是需要提取json中的值，这个是数据处理时最多的场景。PostgreSQL允许这种提取，返回SQL的varchar，比如通过 ->> 和 #>>，这种操作和获取的jsonb直接转换成varchar的差异在于，抹平了不同类型输出的差异。

 

比如，如果json的'a'字段的值，有一些对应数值8, 有一些对应字符串"8"，有一些对应数值8.0，有一些对应字符串"8.0"，这种情况，通过 ->> 返回的就是对应json值直接做str(a)之后的结果，返回：8, 8, 8.0, 8.0，比较容易做后续的加工处理。如果将json值直接转varchar，就会变成: 8, "8", 8.0, "8.0"，后续统一处理的难度增加了很多，因此，在数据处理层面，->> 简化了数值处理的难度。

 

因此，理解 ->> 和 #>> 的作用，可以看做在json内部的解析上，对提取的字段值做了str(a)的操作，并强制统一返回对应的字符串字面量，并转化成SQL的varchar！！！

 

jsonb测试案例

 

1）jsonb的值提取

 

查询json中的字符串类型：

SELECT ('{"book": 50, "edition": "8"}'::jsonb->'edition');

返回jsonb类型，值为"8"，是字符串的json表示：

通过jsonb_typeof查询返回的json对应的数据类型，是将返回做json解析，并做类型推断：

SELECT jsonb_typeof('{"book": 50, "edition": "8"}'::jsonb->'edition');

返回：

2）查询jsonb中不存在的字段

查询不存在的字段，返回json对应的null：

SELECT '{"book": 50, "edition": "8"}'::jsonb->'NG';

返回json对应的null：

探测json对应的null的类型，还是null：

SELECT jsonb_typeof('{"book": 50, "edition": "8"}'::jsonb->'NG');

返回：

不存在的字段的null值和db的null等同。

如：

SELECT '{"book": 50, "edition": "8"}'::jsonb->'NG' is null;

返回：

探测返回的json数值的类型：

SELECT jsonb_typeof('{"book": 50, "edition": "8"}'::jsonb->'book');

返回：

3）jsonb中 -> 和 ->>的差异

对应jsonb中，返回的不同类型的差异，可以通过 ->> 强制转换成 varchar 来抹平！！！

例：

select js->'book', js->'edition', js->'bucket', js->'NG'
from (
    SELECT '{
      "book": 50,
      "edition": "8",
      "bucket":10.0
    }'::jsonb as js
) a;

返回：

可以替换为：

select js->>'book', js->>'edition', js->>'bucket', js->>'NG'
from (
    SELECT '{
      "book": 50,
      "edition": "8",
      "bucket":10.0
    }'::jsonb as js
) a;

返回：

因为 ->> 对应的类型都是db类型中的varchar（与jsonb脱钩），很容易通过postgresql提供的类型转换函数做对应的处理。

例如：

select (js->>'bucket')::float = 10
from (
    SELECT '{
      "book": 50,
      "edition": "8",
      "bucket":10.0
    }'::jsonb as js
) a;

返回：

4）jsonb中字段为object的情况

验证->和->>对obj类型的作用，可以看：

select js->'obj'
from (
    SELECT '{
      "book": 50,
      "edition": "8",
      "bucket":10.0,
      "obj": {
        "a": 1,
        "b": 2
      }
    }'::jsonb as js
) a;

返回：

再看这个：

select js->>'obj'
from (
    SELECT '{
      "book": 50,
      "edition": "8",
      "bucket":10.0,
      "obj": {
        "a": 1,
        "b": 2
      }
    }'::jsonb as js
) a;

返回：

差别在于，一个是jsonb，另外一个是varchar！