Postgres: Populate a JSON field with an array of values from a subquery

Question

I'm trying to populate a new JSON field with existing data and could use some help getting the field properly formatted. I have a subquery returning a list of values intended to be an array in the JSON property. The subquery returns the right results but I can't quite get the JSON array populated properly.

My query is essentially this:

UPDATE foos SET json_property =  
cast(
  '{"bar_ids":' || 
  (select json_agg(x) from (select id from bars) x) || 
  '}'
as json);

What I want to store is this:

{"bar_ids":[1,2,3,4]}

but what I currently get is this:

{"bar_ids":[{"id":1},{"id":2},{"id":3},{"id":4}]}

Thoughts?

clemens · Accepted Answer · 2018-01-24 06:22:48Z

1

It seems that one subquery is needless:

UPDATE foos SET json_property =  
CAST(
  '{"bar_ids":' || 
  (SELECT JSON_AGG(x.id) FROM bars x) || 
  '}'
AS json);

answered Jan 24, 2018 at 6:22

clemens

17.9k12 gold badges52 silver badges71 bronze badges

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

1 Comment

Shawn McBride Over a year ago

Ah, yeah, you're right. That nested subquery was the closest working thing I had after tossing stuff against the wall for a while. I yanked that out in my real query and it's working like a charm now!

Collectives™ on Stack Overflow

Postgres: Populate a JSON field with an array of values from a subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related