如何在数组索引中创建Postgresql JSONB数组？ [英] How do you create a Postgresql JSONB array in array index?

问题描述

我的结构如下：user_id，a。

I have structure like this: user_id, a.

a的类型为jsonb并具有以下结构：

a is of type jsonb and has the following structure:

{ b: 
   [
     {ids: [1,2,3,4]}, 
     {ids: [2,3,4]}, 
     {ids: [1,2,4]}, 
     ...
   ]
}

我如何创建一个索引，使我能够找到ids列表中具有特定ID的所有用户（user_id）？

How would I make an index that enabled me to find all users (user_id) that has a certain id in the ids list?

推荐答案

是 GIN索引您想要什么？

您似乎首先需要将ID组织成更易处理的形式。我比使用PostgreSQL操作JSON的方法更熟悉Python，因此我使用PL / Python来实现此目的。

It seems that you first need to organized the IDs into a form that is more tractable. I'm more familiar with Python than I am with the PostgreSQL ways of manipulating JSON, so I used PL/Python for this purpose.

   DROP TABLE IF EXISTS ids;

CREATE TABLE ids (user_id integer, a jsonb);

INSERT INTO ids VALUES 
    (1, '{"b": [{"ids": [1, 2, 3, 4]}, {"ids": [2, 3, 4]}, {"ids": [1, 2, 4]}]}'),
    (2, '{"b": [{"ids": [2, 3, 4]}]}'),
    (3, '{"b": [{"ids": [4, 5, 6]}, {"ids": [6, 7, 8]}]}');

CREATE OR REPLACE FUNCTION extract_ids(a_json jsonb) 
RETURNS int[] AS
$BODY$
    import json
    s = set()
    a = json.loads(a_json)
    for key in a.keys():
        for id_set in a[key]:
            s.update(id_set['ids'])
    return(list(s))
$BODY$ LANGUAGE plpythonu IMMUTABLE;

SELECT user_id, extract_ids(a)
FROM ids;

CREATE INDEX ON ids USING gin (extract_ids(a));

SELECT user_id 
FROM ids
WHERE ARRAY[3] <@ extract_ids(a);

这篇关于如何在数组索引中创建Postgresql JSONB数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！