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

wi3ka0sx 于 9个月前发布在 PostgreSQL

关注(0)|答案(2)|浏览(119)

我在postgressql中有一个表，其中包含一个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');

字符串
json中的键是随机和无序的。我需要创建一个函数，从SQL查询中的syn_data列中动态提取前N条记录。对于每个usr_id，都有几行包含这种json数据。
我如何为每一行做这件事？
我尝试了一个json_each()函数和一个random over，但是json_each()返回了一个随机排序的键，这不是我所期望的。
例如：当我给予一个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。

postgresql

来源：https://stackoverflow.com/questions/77751479/extract-first-n-values-from-a-json-field-in-postgresql

2条答案

按热度按时间

0wi1tuuw1#

如果您想删除目标范围之外的所有syn_data条目，可以使用jsonb - text[]减法。Demo at 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;

字符串
| 接收ID|？列？|
| --|--|
| 2 |{“1”：{“score”：5.0，“syn_idx”：[724966，125940，727242]，“custom_score”：1.0}，“2”：{“score”：5.0，“syn_idx”：[119294，119321]，“custom_score”：1.0}|
这个translate()将jsonb_path_query_array()产生的jsonb数组中使用的方括号转换为花括号，这样可以将其转换为text[]，这可以与-一起使用。您还可以提取键，然后聚合为text[]，如demo所示。
你也可以使用OFFSET子句删除除了前 * 意外 * N个条目之外的所有键（不“解释”它们的键，只是碰巧首先获取的数量）：

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的建议：提取偶然氮（键，值）对使用jsonb_each()与LIMIT应用，并用jsonb_object_agg(k,v)重建jsonb。常规WHERE可以替换JSONPath过滤器表达式，同时与LIMIT协同工作，当你需要 * 最多2个匹配范围 * 时，这可能很有用：

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

型

赞(0）回复(0）举报 9个月前

kr98yfug2#

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

select  syndata->(num::text)
from    my_table
cross join
        generate_series(1, 2) ser(num)

字符串
Example at DB Fiddle

赞(0）回复(0）举报 9个月前

我来回答

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

2条答案

相关问题

热门标签

最新问答