如何从PostgreSQL json中提取数组

最新推荐文章于 2024-08-11 19:36:34 发布

arcticJian

最新推荐文章于 2024-08-11 19:36:34 发布

阅读量1.2w

点赞数 1

分类专栏： Postgresql

Postgresql 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

转载：https://yq.aliyun.com/articles/60730

如何从PostgreSQL json中提取数组
作者
digoal

日期
2016-09-10

标签
PostgreSQL , json , 数组 , jsonb

背景
在PostgreSQL中使用JSON类型时，除了能存，大家更关心的是JSON中的数据的使用，例如
1. VALUE支持哪些类型，

通过以下方法可以提取JSON最外层的VALUE的数据类型

json_typeof(json)
jsonb_typeof(jsonb)
目前支持的类型如下
object, array, string, number, boolean, and null

包含哪些KEY或VALUE，通过路径提取KEY或VALUE等。
通过制定KEY提取VALUE，
通常通过KEY提取的VALUE还是JSON或JSONB类型，因为JSON本身就是嵌套的，但是可以通过json typeof得知它的类型。

json_typeof(json)
jsonb_typeof(jsonb)
但是SQL还是认为他是个JSON，要么就是转换为TEXT。

postgres=# create table t3(c1 jsonb);
CREATE TABLE
postgres=# insert into t3 values (‘{“a”:”v”,”b”:12,”c”:{“ab”:”hello”},”d”:12.3,”e”:true,”f”:[1,2,3,4],”g”:[“a”,”b”]}’);
INSERT 0 1

postgres=# select pg_typeof(col), jsonb_typeof(col),col from (select c1->’a’ col from t3) t;
pg_typeof | jsonb_typeof | col
———–+————–+—–
jsonb | string | “v”
(1 row)

postgres=# select pg_typeof(col), jsonb_typeof(col),col from (select c1->’b’ col from t3) t;
pg_typeof | jsonb_typeof | col
———–+————–+—–
jsonb | number | 12
(1 row)

postgres=# select pg_typeof(col), jsonb_typeof(col),col from (select c1->’c’ col from t3) t;
pg_typeof | jsonb_typeof | col
———–+————–+—————–
jsonb | object | {“ab”: “hello”}
(1 row)

postgres=# select pg_typeof(col), jsonb_typeof(col),col from (select c1->’d’ col from t3) t;
pg_typeof | jsonb_typeof | col
———–+————–+——
jsonb | number | 12.3
(1 row)

postgres=# select pg_typeof(col), jsonb_typeof(col),col from (select c1->’e’ col from t3) t;
pg_typeof | jsonb_typeof | col
———–+————–+——
jsonb | boolean | true
(1 row)

postgres=# select pg_typeof(col), jsonb_typeof(col),col from (select c1->’f’ col from t3) t;
pg_typeof | jsonb_typeof | col
———–+————–+————–
jsonb | array | [1, 2, 3, 4]
(1 row)

postgres=# select pg_typeof(col), jsonb_typeof(col),col from (select c1->’g’ col from t3) t;
pg_typeof | jsonb_typeof | col
———–+————–+————
jsonb | array | [“a”, “b”]
(1 row)
4. 特定VALUE类型的处理，例如数组。

当VALUE是ARRAY时，如果需要返回ARRAY给SQL层，暂时还没有内置这样的操作符，需要自定义，本文将介绍。

如何返回JSON中存储的数组
大多数JSON操作符，返回的要么是TEXT要么是JSON.

例如以下查询
操作符->返回数组

postgres=# select pg_typeof(‘{“a”:[1,2,3],”b”:[4,5,6]}’::json->’a’), ‘{“a”:[1,2,3],”b”:[4,5,6]}’::json->’a’;
pg_typeof | ?column?
———–+———-
json | [1,2,3]
(1 row)
操作符->>返回text

postgres=# select pg_typeof(‘{“a”:[1,2,3],”b”:[4,5,6]}’::json->>’a’), ‘{“a”:[1,2,3],”b”:[4,5,6]}’::json->>’a’;
pg_typeof | ?column?
———–+———-
text | [1,2,3]
(1 row)
如果明确json_typeof或jsonb_typeof是array，则可以通过以下function将数组转换为行
不管是什么数组，都返回text行

json_array_elements_text(json)

jsonb_array_elements_text(jsonb)

postgres=# select pg_typeof(col),col from (select json_array_elements_text(‘{“a”:”B”,”b”:[1,2,3,4,5,6]}’::json->’b’) col) t;
pg_typeof | col
———–+—–
text | 1
text | 2
text | 3
text | 4
text | 5
text | 6
(6 rows)
使用数组构造器，可以将多条记录构造为数组。

postgres=# SELECT array(select json_array_elements_text(‘{“a”:”B”,”b”:[1,2,3,4,5,6]}’::json->’b’));

array

{1,2,3,4,5,6}
(1 row)

postgres=# SELECT pg_typeof(array(select json_array_elements_text(‘{“a”:”B”,”b”:[1,2,3,4,5,6]}’::json->’b’)));

pg_typeof

text[]
(1 row)
如何转换JSONB数组的类型
如果想构造int[]，在构造前，可以将行的输出转换为对应的格式，

postgres=# SELECT array(select (json_array_elements_text(‘{“a”:”B”,”b”:[1,2,3,4,5,6]}’::json->’b’))::int );

array

{1,2,3,4,5,6}
(1 row)

postgres=# SELECT pg_typeof(array(select (json_array_elements_text(‘{“a”:”B”,”b”:[1,2,3,4,5,6]}’::json->’b’))::int ));

pg_typeof

integer[]
(1 row)

编写JSON数组转换为SQL数组的函数
可以将以上方法转换为函数来使用，输入json typeof == array的json或jsonb对象，输出text数组。
转换jsonb, json array to text array

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js jsonb)
RETURNS text[] AS
$func$
SELECT ARRAY(SELECT jsonb_array_elements_text(_js))
$func$
LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
RETURNS text[] AS
$func$
SELECT ARRAY(SELECT json_array_elements_text(_js))
$func$
LANGUAGE sql IMMUTABLE;
转换jsonb, json array to int array

CREATE OR REPLACE FUNCTION json_arr2int_arr(_js jsonb)
RETURNS int[] AS
$func$
SELECT ARRAY( SELECT (jsonb_array_elements_text(_js))::int )
$func$
LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION json_arr2int_arr(_js json)
RETURNS int[] AS
$func$
SELECT ARRAY( SELECT (json_array_elements_text(_js))::int )
$func$
LANGUAGE sql IMMUTABLE;
例子

postgres=# select col, pg_typeof(col) from (select json_arr2text_arr(c1->’f’) col from t3) t;
col | pg_typeof
———–+———–
{1,2,3,4} | text[]
(1 row)

postgres=# select col, pg_typeof(col) from (select json_arr2int_arr(c1->’f’) col from t3) t;
col | pg_typeof
———–+———–
{1,2,3,4} | integer[]
(1 row)

postgres=# select col, pg_typeof(col) from (select json_arr2text_arr(c1->’g’) col from t3) t;
col | pg_typeof
——-+———–
{a,b} | text[]
(1 row)
应用场景
例如业务系统在JSON中存储了一些ARRAY，这些ARRAY通常会包括一些全包含，全不包含，相交等查询，达到查询的目的。

这里就需要用到数组的包含，相交，不相干操作符来判断，那就会用到前面提到的数组的转换需求。

例子

例如JSON存储的是乐高积木的各属性。

JSON某个KEY存储的数组代表乐高积木对应型号的小零件部件号清单，然后用户需要查询哪些积木型号包含了指定零件编号，这里涉及的就是全包含的查询。

包含’a’零件

postgres=# select * from t3 where json_arr2text_arr(c1->’g’) @> array[‘a’];

c1

{“a”: “v”, “b”: 12, “c”: {“ab”: “hello”}, “d”: 12.3, “e”: true, “f”: [1, 2, 3, 4], “g”: [“a”, “b”]}
(1 row)
同时包含’a’,’c’零件

postgres=# select * from t3 where json_arr2text_arr(c1->’g’) @> array[‘a’,’c’];

c1

(0 rows)
同时包含1,2零件

postgres=# select * from t3 where json_arr2int_arr(c1->’f’) @> array[1,2];

c1

{“a”: “v”, “b”: 12, “c”: {“ab”: “hello”}, “d”: 12.3, “e”: true, “f”: [1, 2, 3, 4], “g”: [“a”, “b”]}
(1 row)
同时包含1,6零件

postgres=# select * from t3 where json_arr2int_arr(c1->’f’) @> array[1,6];

c1

(0 rows)
包含1或6零件

postgres=# select * from t3 where json_arr2int_arr(c1->’f’) && array[1,6];

c1

{“a”: “v”, “b”: 12, “c”: {“ab”: “hello”}, “d”: 12.3, “e”: true, “f”: [1, 2, 3, 4], “g”: [“a”, “b”]}
(1 row)
即不包含1 也不包含6零件

postgres=# select * from t3 where not(json_arr2int_arr(c1->’f’) && array[1,6]);

c1

(0 rows)
不包含6零件

postgres=# select * from t3 where not(json_arr2int_arr(c1->’f’) && array[6]);

c1

{“a”: “v”, “b”: 12, “c”: {“ab”: “hello”}, “d”: 12.3, “e”: true, “f”: [1, 2, 3, 4], “g”: [“a”, “b”]}
(1 row)
如果你不想转换为INT，那么在条件中也不要使用INT数组

postgres=# select * from t3 where not(json_arr2text_arr(c1->’f’) && array[‘6’]);

c1

{“a”: “v”, “b”: 12, “c”: {“ab”: “hello”}, “d”: 12.3, “e”: true, “f”: [1, 2, 3, 4], “g”: [“a”, “b”]}
(1 row)
索引
为了提升这种查询的速度，我们可以这样建立索引。

postgres=# create index idx_t3_1 on t3 using gin (json_arr2text_arr(c1->’f’));
CREATE INDEX
postgres=# set enable_seqscan=off;
SET
postgres=# explain select * from t3 where json_arr2text_arr(c1->’f’) && array[‘1’,’6’];

QUERY PLAN

Bitmap Heap Scan on t3 (cost=12.25..16.52 rows=1 width=32)
Recheck Cond: (json_arr2text_arr((c1 -> ‘f’::text)) && ‘{1,6}’::text[])
-> Bitmap Index Scan on idx_t3_1 (cost=0.00..12.25 rows=1 width=0)
Index Cond: (json_arr2text_arr((c1 -> ‘f’::text)) && ‘{1,6}’::text[])
(4 rows)

postgres=# explain select * from t3 where json_arr2text_arr(c1->’f’) @> array[‘1’,’6’];

QUERY PLAN

Bitmap Heap Scan on t3 (cost=12.25..16.52 rows=1 width=32)
Recheck Cond: (json_arr2text_arr((c1 -> ‘f’::text)) @> ‘{1,6}’::text[])
-> Bitmap Index Scan on idx_t3_1 (cost=0.00..12.25 rows=1 width=0)
Index Cond: (json_arr2text_arr((c1 -> ‘f’::text)) @> ‘{1,6}’::text[])
(4 rows)