CODEWITHSUNDEEP
arraysjsonpostgresql
balkon16
balkon16

Reputation: 1438

Postgresql get elements of a JSON array

Let's say that we have the following JSON in Postgresql:

{ "name": "John", "items": [ { "item_name": "lettuce", "price": 2.65, "units": "no" }, { "item_name": "ketchup", "price": 1.51, "units": "litres" } ] }

The JSONs are stored in the following table:

create table testy_response_p (
 ID serial NOT NULL PRIMARY KEY,
 content_json json NOT NULL
)

insert into testy_response_p (content_json) values (
'{ "name": "John", "items": [ { "item_name": "lettuce", "price": 2.65, "units": "no" }, { "item_name": "ketchup", "price": 1.51, "units": "litres" } ] }'
)

Since the following can return either JSON or text (with -> and ->> respectively select content_json ->> 'items' from testy_response_p) I want to use a subquery in order to get elements of the array under items:

select *
from json_array_elements(
select content_json ->> 'items' from testy_response_p
)

All I get is an error but I don't know what I'm doing wrong. The output of the subquery is text. The final output is:

{ "item_name": "lettuce", "price": 2.65, "units": "no" }
{ "item_name": "ketchup", "price": 1.51, "units": "litres" }

Upvotes: 0

Views: 80

Answers (1)

user330315
user330315

Reputation:

You need to join to the function's result. You can't use the ->> operator because that returns text, not json and json_array_elements() only works with a JSON value for its input.

select p.id, e.*
from testy_response_p p
  cross join lateral json_array_elements(p.content_json -> 'items') as e;

Online example: https://rextester.com/MFGEA29396

Upvotes: 2

Related Questions