在PostgreSQL中，从JSON字段提取前N个值

Question

我有一个在PostgreSQL中的表，其中包含一个存储JSON对象的列：

CREATE TABLE test (
    rec_id int GENERATED BY DEFAULT AS IDENTITY,
    usr_id int UNIQUE,
    format text[],
    syn_data jsonb,
    generation_date timestamptz
);

INSERT INTO test VALUES 
(2,
144,
'{audio,image,text}',
'{
    "7458": {"syn_idx": [724966,125940,727242], "score": 5.0, "custom_score": 1.0},
    "1742": {"syn_idx": [119294,119321], "score": 5.0, "custom_score": 1.0},
    "38521": {"syn_idx": [654145], "score": 5.0, "custom_score": 1.0},
    "4154": {"syn_idx": [617595,348300], "score": 5.0, "custom_score": 1.0}
}',
'2023-07-19 06:29:51.584859');

对于每行数据，syn_data列中的键是随机且无序的。我需要创建一个函数，在SQL查询中动态地从syn_data列提取前N条记录。

例如：当我给定参数2时，我希望获取syn_data列中的前2条记录：

{
  "7458": {
    "syn_idx": [724966, 125940, 727242],
    "score": 5.0,
    "custom_score": 1.0
  },
  "1742": {
    "syn_idx": [119294, 119321],
    "score": 5.0,
    "custom_score": 1.0
  }
}

我正在使用PostgreSQL 14.1版本。

duffymo · Answer

在syndata列中的JSON是一个字典。您希望获取其中具有键"1"、"2"、"3"等的前几个元素。您可以使用generate_series生成这些键，然后使用->操作符从JSON列中检索它们：

SELECT syndata -> (num::text)
FROM my_table
CROSS JOIN generate_series(1, 2) AS ser(num);

在DB Fiddle上的示例

Dan Moulding · Answer

如果你想剔除syn_data中所有不在目标范围内的条目，可以利用jsonb - text[]减法运算符。请参考db<>fiddle上的演示示例：

select rec_id
     , syn_data - translate(
                     jsonb_path_query_array( syn_data
                                            ,'$.keyvalue().key
                                               ?(@.double()<$min ||
                                                 @.double()>$max)'
                                            ,jsonb_build_object('min',0,
                                                                'max',2))::text
                     ,'[]'
                     ,'{}')::text[]
from test;

该代码段中，translate()函数将jsonb_path_query_array()函数得到的jsonb数组中的方括号转换为花括号，以便将其转换成text[]类型，并配合-运算符使用。您也可以先提取出键，然后将它们聚合到一个text[]中，如示例中所示。

另外，如果您想去除除了前N个“偶然”条目（不依赖于对键本身的解释，只是简单地去除最先获取到的N个条目），可以使用OFFSET子句：

select rec_id
     , syn_data - remove_these
from test,
     lateral (
          select array_agg(remove_these::text) as remove_these
          from (
               select jsonb_object_keys(syn_data) as remove_these 
               offset 2
          ) as g1
     ) as g2;

或者采取类似@Charlieface建议的方法，使用带有LIMIT限制条件的jsonb_each()
函数来偶然性地提取前N个（键，值）对，然后通过jsonb_object_agg(k,v)重构为一个新的jsonb对象。结合LIMIT使用的常规WHERE条件可以替换JSONPath过滤表达式，当需要从匹配范围内最多选取2个条目时非常有用：

select rec_id, jsonb_object_agg(k,v)
from test,
     lateral (
          select k, v from jsonb_each(syn_data) as _(k, v)
          where k::int between 0 and 2
          limit 2
     )
group by rec_id;
```