在表列上使用 Postgres JSON 函数 [英] Using Postgres JSON Functions on table columns

问题描述

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

I have searched extensively (in Postgres docs and on Google and SO) to find examples of JSON functions being used on actual JSON columns in a table.

这是我的问题:我正在尝试使用 jsonb_to_recordset() 从列中的 JSON 对象数组中提取键值，但出现语法错误.当我将对象逐字传递给函数时，它工作正常:

Here's my problem: I am trying to extract key values from an array of JSON objects in a column, using jsonb_to_recordset(), but get syntax errors. When I pass the object literally to the function, it works fine:

按字面传递 JSON:

Passing JSON literally:

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"的值.

It extracts the value of the key "name".

这是列中的 JSON，使用以下方法提取:

Here's the JSON in the column, being extracted using:

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#>>'{context,data,files}' 传递给 jsonb_to_recordset() 时:

But when I try to pass jsonb#>>'{context,data,files}' to jsonb_to_recordset() like this:

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

我收到一个语法错误.我尝试了不同的方法，但每次都抱怨语法错误:

I get a syntax error. I have tried different ways but each time it complains about a syntax error:

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

Version: PostgreSQL 9.4.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

推荐答案

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

The expressions after select must evaluate to a single value. Since jsonb_to_recordset returns a set of rows and columns, you can't use it there.

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

The solution is a cross join lateral, which allows you to expand one row into multiple rows using a function. That gives you single rows that select can act on. For example:

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'

注意#>> 运算符 返回类型 text，#> 运算符返回类型 jsonb.由于 jsonb_to_recordset 期望 jsonb 作为它的第一个参数，我使用 #>.

Note that the #>> operator returns type text, and the #> operator returns type jsonb. As jsonb_to_recordset expects jsonb as its first parameter I'm using #>.

在 rextester.com 上查看它的运行情况

这篇关于在表列上使用 Postgres JSON 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！