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

Andomar · Accepted Answer · 2017-03-08 18:26:36Z

19

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

edited Mar 8, 2017 at 18:26

answered Mar 8, 2017 at 18:11

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dmfay · Accepted Answer · 2017-03-08 18:20:32Z

jsonb_to_recordset is a set-valued function and can only be invoked in specific places. The FROM clause is one such place, which is why your first example works, but the SELECT clause is not.

In order to turn your JSON array into a "table" that you can query, you need to use a lateral join. The effect is rather like a foreach loop on the source recordset, and that's where you apply the jsonb_to_recordset function. Here's a sample dataset:

create table jstuff (id int, val jsonb);

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

A simple lateral join query:

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)

That's the hard part. Note that you have to define what your new recordset looks like in the AS clause -- since each element in our val array is a JSON object with a single field named "outer", that's what we give it. If your array elements contain multiple fields you're interested in, you declare those in a similar manner. Be aware also that your JSON schema needs to be consistent: if an array element doesn't contain a key named "outer", the resulting value will be null.

From here, you just need to pull the specific value you need out of each JSON object using the traversal operator as you were. If I wanted only the "inner" value from the sample dataset, I would specify select id, r.outer->>'inner'. Since it's already JSONB, it doesn't require casting.

Jyotsana · Accepted Answer · 2024-02-08 17:52:02Z

0

I had a similar use case where I wanted to fetch the key and its corresponding values from an array of JSON objects.

Postgres database :

Table Name: person_details
Column name: Details
--------------------
[
{"state": "Hyderabad", "name": "John", "language": "telugu"}, 
{"state": "jaipur", "name": "james", "language": "Marathi"}
]

Now I am going to split this array of JSON objects based on the key that is key would become a column name.

SELECT x.state,x.name,x.language FROM personal_details CROSS JOIN LATERAL json_to_recordset(personal_details.Details) as x(state Text,name Text,language Text);

Output: Query Output

edited Feb 8, 2024 at 17:52

answered Feb 8, 2024 at 17:51

Jyotsana

12 bronze badges

1 Comment

ElRojo Over a year ago

Nice formatting. Welcome to Stack Overflow. This answer might be more useful if you make it clear what you are providing that was not provided in the other two answers. Also Could you please specify what you are doing that makes this work better than the code in the question? For example, is the Cross Join Lateral the important part?

Collectives™ on Stack Overflow

Using Postgres JSON Functions on table columns

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related