Using Postgres JSON Functions on table columns

Question

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.

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:

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);`

results in:

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

It extracts the value of the key "name".

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

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

resulting in:

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

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:

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

Answer 1

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.

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'

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 #>.

See it working at rextester.com