文章/答案/技术大牛

发布

社区首页 >问答首页 >对表列使用Postgres JSON函数

问对表列使用Postgres JSON函数
EN

Stack Overflow用户

提问于 2017-03-09 01:01:40

回答 2查看 6.9K关注 0票数 9

我进行了广泛的搜索(在Postgres文档和Google上等)，以找到在表中实际的JSON列上使用的JSON函数的示例。

这就是我的问题:我试图使用jsonb_to_recordset()从列中的JSON对象数组中提取键值，但得到了语法错误。当我将对象逐字传递给函数时，它工作得很好：

逐字传递JSON：

select * 
from jsonb_to_recordset('[
    { "id": 0, "name": "400MB-PDF.pdf", "extension": ".pdf", 
        "transferId": "ap31fcoqcajjuqml6rng"}, 
    { "id": 0, "name": "1000MB-PDF.pdf", "extension": ".pdf", 
      "transferId": "ap31fcoqcajjuqml6rng"}
  ]') as f(name text);`

结果如下：

400MB-PDF.pdf
1000MB-PDF.pdf

它提取键"name“的值。

下面是列中的JSON，使用以下命令提取：

select journal.data::jsonb#>>'{context,data,files}' 
from journal 
where id = 'ap32bbofopvo7pjgo07g';

结果是：

[ { "id": 0, "name": "400MB-PDF.pdf", "extension": ".pdf", 
    "transferId": "ap31fcoqcajjuqml6rng"}, 
  { "id": 0, "name": "1000MB-PDF.pdf", "extension": ".pdf", 
    "transferId": "ap31fcoqcajjuqml6rng"}
]

但是当我尝试像这样向jsonb_to_recordset()传递jsonb#>>'{context，data，files}‘时：

select id, 
       journal.data::jsonb#>>::jsonb_to_recordset('{context,data,files}') as f(name text) 
from journal 
where id = 'ap32bbofopvo7pjgo07g';

我得到一个语法错误。我尝试了不同的方法，但每次它都会抱怨语法错误：

版本: x86_64-unknown-linux-gnu上的PostgreSQL 9.4.10，由gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2编译，64位

sql

json

postgresql

回答 2

Stack Overflow用户

回答已采纳

发布于 2017-03-09 02:11:42

select之后的表达式必须计算为单个值。因为jsonb_to_recordset返回一组行和列，所以您不能在那里使用它。

解决方案是cross join lateral，它允许您使用函数将一行扩展为多行。这为您提供了select可以操作的单行。例如：

select  *
from    journal j
cross join lateral
        jsonb_to_recordset(j.data#>'{context, data, files}') as d(id int, name text)
where   j.id = 'ap32bbofopvo7pjgo07g'

请注意，#>> operator返回类型text，而#>操作符返回类型jsonb。因为jsonb_to_recordset期望jsonb作为它的第一个参数，所以我使用#>。

See it working at rextester.com

票数 10

Stack Overflow用户

发布于 2017-03-09 02:20:32

jsonb_to_recordset是一个集值函数，只能在特定的地方调用。FROM子句就是这样一个地方，这就是为什么您的第一个示例可以工作，但SELECT子句不行的原因。

为了将JSON数组转换为可以查询的“表”，需要使用横向连接。其效果非常类似于源记录集上的foreach循环，这就是您应用jsonb_to_recordset函数的地方。下面是一个示例数据集：

create table jstuff (id int, val jsonb);

insert into jstuff
values
    (1, '[{"outer": {"inner": "a"}}, {"outer": {"inner": "b"}}]'),
    (2, '[{"outer": {"inner": "c"}}]');

一个简单的横向连接查询：

select id, r.*
from jstuff
join lateral jsonb_to_recordset(val) as r("outer" jsonb) on true;

 id |     outer      
----+----------------
  1 | {"inner": "a"}
  1 | {"inner": "b"}
  2 | {"inner": "c"}
(3 rows)

这正是难点。请注意，您必须在JSON子句中定义新记录集的外观--因为val数组中的每个元素都是一个JSON对象，其中只有一个字段名为"outer"，这就是我们提供的内容。如果数组元素包含多个您感兴趣的字段，则可以用类似的方式声明这些字段。还要注意，您的JSON模式需要保持一致:如果数组元素不包含名为“your”的键，则结果值将为null。

在这里，您只需要像以前一样使用遍历运算符从每个JSON对象中提取需要的特定值。如果我只想要样本数据集中的“内部”值，我会指定select id, r.outer->>'inner'。因为它已经是JSONB，所以不需要强制转换。

票数 8

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/42677415

复制

相似问题

问对表列使用Postgres JSON函数
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问对表列使用Postgres JSON函数EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问对表列使用Postgres JSON函数
EN