sql - 从 postgreSQL 中的 JSON 数组获取单个元素

Question

我需要根据我定义的一些规则从存储在 postgreSQL 数据库中的数组中检索单个元素，然后将它与其他一些数据连接起来。让我举个例子:

postgres=# SELECT * FROM list;
id    | list
------+-----------------------------------------------------------------------------
0ab2c | [{type: 1, val: a}, {type: 3, val: c}, {type: 4, val: d}]
01abc | [{type: 1, val: a}, {type: 2, val: b}]
a0b31 | [{type: 1, val: a}, {type: 2, val: b}, {type: 3, val: c}]

postgres=# SELECT * FROM elems
eid | listId
----+-------
10  | 0ab2c
11  | 01abc
12  | a0b31

我想从 list 表中提取仅一个元素，然后将结果与 elem 表连接起来，以获得如下内容:

eid | listId | type | val
----+--------+------+-----
10  | 0ab2c  |   3  |  c
11  | 01abc  |   2  |  b
12  | a0b31  |   2  |  b

特别是json数组中的元素应该根据一个函数来选择(我用JS写的，因为我比较熟悉，但是这个函数当然需要在plpgSQL中):

function getElemFromList(list){
    var allowedTypes = [2, 3];
    var result;
    for(var i = 0; i < list.length; i++){
        if(list[i].indexOf(allowedTypes) !== -1){
            return list[i];
        }
    }
}

列表中的元素属于特定类型并且是数组中第一个允许的元素。

Answer 1

这应该很简单。看起来您需要此页面中的运算符:

http://www.postgresql.org/docs/current/static/functions-json.html

如果你想遍历 pl/pgsql 中的任何结果集，那么看看这样的东西:

DO $$
cur RECORD;
BEGIN
FOR cur IN SELECT * FROM LIST
//do something with your JSON
END LOOP;
END $$;