sql - 在表列上使用 Postgres JSON 函数

Question

我进行了广泛的搜索(在 Postgres 文档以及 Google 和 SO 中)以找到在表中的实际 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#>>'{context,data,files}' 传递给 jsonb_to_recordset() 时:

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 位编译

Answer 1

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

解决方案是横向交叉连接，它允许您使用一个函数将一行扩展为多行。这为您提供了 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