如何把一个简单的JSON（二）int数组到PostgreSQL中的一个整数[] 9.4+ [英] How to turn a simple json(b) int array into an integer[] in PostgreSQL 9.4+

查看：439
本文介绍了如何把一个简单的JSON（二）int数组到PostgreSQL中的一个整数[] 9.4+的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个JSON对象数组： [1，9，12]

I have this array from a json object: [1, 9, 12]

由于它采用的方括号，因为它是从一个JSON对象直接拿来，我不能将它转换为 ::整数[] ，当我尝试使用 ARRAY_AGG（jsonb_array_elements（simpleintarray））我得到一个错误说我需要GROUP BY ID，但由于数组是不是对象（键/值）对，但只是简单的整数，我不能看看如何在一个相当有效的方式实现这一目标。

Since it uses the square bracket notation because it is fetched directly from a json object I cannot cast it to ::integer[] and when I try to use array_agg(jsonb_array_elements(simpleintarray)) I get an error saying I need to group by id, but since the array is not objects (key/value) pairs but just simple integers, I cannot see how to achieve this in a rather efficient manner.

该查询返回的JSON从上面简单的int数组是：

The query returning the above simple int array from json is:

SELECT node.*, elem->'permissions' AS group_node_permissions
    FROM node
    LEFT OUTER JOIN
    jsonb_array_elements(my_user_group.node_permissions) elem
    ON elem->>'id' = node.id::text
    ORDER BY node.id

elem-＆GT;的权限理论上应该返回格式一个Postgres阵列 {} 等等后来我才可以使用它的 ANY（intarray）功能。

elem->'permissions' should ideally be returned as a Postgres array in the format {} so I can later use the ANY(intarray) function on it.

我想避免做重复低效的解决方法如车削 elem-＆GT;的权限与字符串 - ＆GT;＆GT ; 用大括号替换方括号，然后浇铸成一个整数数组，尽管这可能会正常工作

I'd like to avoid doing redundant inefficient workarounds such as turning elem->'permissions' into a string with ->> replacing square brackets with curly brackets and then casting into a integer array, although that would probably work.

在伪code我真正需要的是能够得到这个相同的结果：

In pseudo code what I really need is to be able to get this same result:

SELECT *节点，elem-方式＆gt;权限** ::整数[] ** AS group_node_permissions，

...但当然，由于 [] VS {} 从JSON阵列的区别PostgreSQL的阵列格式，这将导致错误。

... but of course due to the [] vs {} difference from the json array to the PostgreSQL array format, this will result in an error.

这是我目前（非常丑陋的解决方案，它的工作原理）：

This is my current (VERY ugly solution which works):

SELECT node.*, replace(replace(elem->>'permissions', '[', '{'),']','}')::integer[] AS group_node_permissions

原来原始的 [1，9，12] （jsonb）插入表格 {1,9,12} （整数[]）

It turns the original [1, 9, 12] (jsonb) into the form {1,9,12} (integer[])

有没有什么更好的方案来解决这个？

Are there any better solutions for this?

P.S。

它甚至值得从JSON（B）转换为int数组（[]），您可以用 jsonarray @＆GT获取数组元素; '12'来Postgres的整数[] 数组，你可以使用 12 = ANY（intarray）。有没有人有任何想法，其中更高性能和规模应该更好？现在，我们可以把阵列中的 jsonb 数据类型的列，这被认为是在preferred大大超过如。在整数[] 数据类型？

Is it even worth converting from the json(b) to an int array ([]) where you can fetch the array elements with jsonarray @> '12' to the Postgres integer[] array where you can use 12 = ANY(intarray). Has anyone got any idea of which is more performant and should scale better? Now that we can put arrays in columns of the jsonb data type, is this considered the preferred way over eg. the integer[] data type?

扩展信息（每个请求由欧文）： 

SELECT DISTINCT ON (my_node.id) my_node.*
FROM user_group AS my_user_group,
LATERAL
(
    SELECT node.*, elem->'permissions' AS user_group_node_permissions
    FROM node
    LEFT OUTER JOIN
    jsonb_array_elements(my_user_group.node_permissions) elem
    ON elem->>'id' = node.id::text
    ORDER BY node.id
)my_node
WHERE (my_user_group.id = ANY('{2,3}')) --try also with just: ANY('{3}')) to see node 3 is excluded
AND (user_group_node_permissions @> '12' OR (user_group_node_permissions IS NULL AND 12 = ANY(my_user_group.default_node_permissions)));

DDL：

CREATE TABLE node
(
  id bigserial NOT NULL,
  path ltree,
  name character varying(255),
  node_type smallint NOT NULL,
  created_by bigint NOT NULL,
  created_date timestamp without time zone NOT NULL DEFAULT now(),
  parent_id bigint,
  CONSTRAINT node_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE user_group
(
  id serial NOT NULL,
  name character varying,
  alias character varying,
  node_permissions jsonb,
  section_ids jsonb,
  default_node_permissions jsonb
)
WITH (
  OIDS=FALSE
);

DML：

节点：

INSERT INTO node VALUES (1, '1', 'root', 5, 1, '2014-10-22 16:51:00.215', NULL);

INSERT INTO node VALUES (2, '1.2', 'Home', 1, 1, '2014-10-22 16:51:00.215', 1);
INSERT INTO node VALUES (3, '1.2.3', 'Sample Page', 1, 1, '2014-10-22 16:51:00.215', 2);
INSERT INTO node VALUES (4, '1.2.3.4', 'Child Page Level 1', 1, 1, '2014-10-26 23:19:44.735', 3);
INSERT INTO node VALUES (5, '1.2.3.4.5', 'Child Page Level 2', 1, 1, '2014-10-26 23:19:44.735', 4);

INSERT INTO node VALUES (6, '1.2.6', 'Test Page', 1, 1, '2014-12-01 11:45:16.186', 2);
INSERT INTO node VALUES (7, '1.2.7', 'Login', 1, 1, '2014-12-01 11:54:10.208', 2);
INSERT INTO node VALUES (8, '1.2.7.8', 'MySubPage', 1, 1, '2014-12-01 12:02:54.252', 7);
INSERT INTO node VALUES (9, '1.2.9', 'Yet another test page', 1, 1, '2014-12-01 12:07:29.999', 2);

INSERT INTO node VALUES (10, '1.2.10', 'Testpage 2', 1, 1, '2014-12-02 01:43:33.233', 2);
INSERT INTO node VALUES (11, '1.2.10.11', 'Test page 2 child', 1, 1, '2014-12-02 01:45:49.78', 10);

组：

INSERT INTO user_group VALUES (2, 'Editor', 'editor', NULL, NULL, '{1,2,3,4,5,7,9,10,12}');
INSERT INTO user_group VALUES (1, 'Administrator', 'administrator', NULL, NULL, '{1,2,3,4,5,6,7,8,9,10,11,12}');
INSERT INTO user_group VALUES (3, 'Writer', 'writer', '[{"id": 3, "permissions": [1, 9]}, {"id": 4, "permissions": [1, 9, 12]}]', NULL, '{1,3,9,12}');

简短说明：

基本上我在这里做的是：

Basically what I'm doing here is this:


  • 一个用户可以有多个组（无论是作为整数[]数据类型或jsonb阵[] - 尚未决定，但考虑欧文的回答在考虑，整数可能是最好的，因为它不应该举行一个大阵

  • 每个组可被分配到特定节点的特定的访问*（见下面的图片，这也解释了外部左连接），从而否决组默认全局权限（许可12是BTW浏览节点，从而获得能力它查询返回的）

  • 自作家组确实有12（浏览）权限，但对于节点的节点权限ID为3没有权限12，只有组的用户作家不会得到节点在由选择查询返回的结果ID 3。但是，如果用户还拥有另一组，并且不排除节点 - 当然，这个节点将被退回，因为更多的强大的群体superseeds的弱的人

性能缓慢 - 可以对它进行优化

（你可以放大在浏览器中图片）

(You can zoom in on the pictures in the browser)

，一个简单的SELECT * FROM节点0.046ms执行（与实测EXPLAIN重新分析）

Compared to the above, a simple SELECT * FROM node executes in 0.046ms (measured with EXPLAIN ANALYZE again)

如果你仍然可以使用更多的信息，请随时提出。

If you still could use even more info, please feel free to ask.

推荐答案

显然，你有一个JSON数组嵌套外JSON数组里面：

Obviously you have a JSON array nested inside an outer JSON array:

SELECT n.*, array_agg(p)::int[] AS group_node_permissions
FROM   my_user_group u
     , jsonb_array_elements(u.node_permissions) elem
JOIN   node n ON n.id = (elem->>'id')::int
     , jsonb_array_elements_text(elem->'permissions') p
GROUP  BY n.id;  -- id being the PK


  • 这是假设有在权限没有空阿拉斯。否则你需要 LEFT JOIN横向... ON TRUE ：

    • This is assuming there are no empty arras in permissions. Else you need LEFT JOIN LATERAL ... ON TRUE:

      • Call a set-returning function with an array argument multiple times

      这应该preserve JSON数组的原始顺序，但没有保证。如果你需要确保，使用与序数。

      This should preserve the original order of the JSON array, but there are no guarantees. If you need to make sure, use WITH ORDINALITY.

      • PostgreSQL unnest() with element number

      LEFT [OUTER] JOIN 将是毫无意义的，因为在左表势力的一列以后的predicate [ INNER] JOIN 行为反正。

      LEFT [OUTER] JOIN would be pointless, since the later predicate on a column of the left table forces [INNER] JOIN behavior anyways.

      在dba.SE相关答案更多的细节和解释：

      Related answer on dba.SE with more details and explanation:

      • How to turn json array into Postgres array?

      根据不同的使用情况的详细信息，这可能是一个好主意，支持与GIN索引的查询：

      Depending on details of the use case, it might be a good idea to support the query with a GIN index:

      • Index for finding an element in a JSON array

      至于您的 P.S。，这取决于全貌。所有其他的考虑预留一个Postgres阵列通常是一个有点小，比 jsonb 快拿着一个JSON阵列。测试一个元素的存在，可以非常快速与一个GIN索引两种方式：

      As for your P.S., it depends on the complete picture. All other considerations aside a Postgres array is typically a bit smaller and faster than jsonb holding a JSON array. Testing for existence of an element can be very fast with with a GIN index either way:

      jsonarray @> '12'
intarray @> '{12}'

      请特别注意，该变种 12 = ANY（intarray）是不会由GIN索引支持。在手册中详细信息。

      Note in particular, that the variant 12 = ANY(intarray) is not supported by a GIN index. Details in the manual.

      这篇关于如何把一个简单的JSON（二）int数组到PostgreSQL中的一个整数[] 9.4+的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆