How to add a key to a JSON array value?

Question

I am new to Postgres and using version 9.4. I have a query returning a json column.
How can I add a key to a JSON array value?

My query:

select array_to_json(array_agg(t))
from (select DISTINCT ON(city,state)latitudes,longitudes,city,state
      from zips where city ilike 'ORL%'
      order by city,state,ziptype desc
      limit 10) t;

The output is like:

[{"latitudes": 31.22,"longitudes": -103.91,"city": "Orla","state": "TX"}, ...

However, I would like to name it such as:

["Locations": [{"latitudes": 31.22,"longitudes": -103.91,"city": "Orla","state": "TX"}, ...

json_build_object('Locations', array_to_json(array_agg(t))) — Abelisto
– Abelisto, Commented Apr 13, 2016 at 19:41

Community · Accepted Answer · 2017-05-23 12:07:40Z

2

Like @Abelisto commented, use json_build_object() (or jsonb_build_object()) to attach a key to your value.
And the simpler json_agg(t) (or jsonb_agg(t)) instead of array_to_json(array_agg(t)):

SELECT json_build_object('Locations', json_agg(t))
FROM  (
   SELECT DISTINCT ON (city, state)
          latitudes, longitudes, city, state
   FROM   zips
   WHERE  city ILIKE 'ORL%'
   ORDER  by city, state, ziptype DESC
   LIMIT  10
   ) t;

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Apr 13, 2016 at 22:18

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Abelisto Over a year ago

BTW It seems that PostgreSQL 9.4 (mentioned in the Q tags) does not provides some functions for the jsonb type unlike the 9.5 version.

Erwin Brandstetter Over a year ago

@Abelisto: Yes, jsonb_agg() is new in Postgres 9.5. You can substitute with json_agg(t)::jsonb in pg 9.4. Either way, the presented query works for pg 9.4 (the OP requested json, not jsonb).

Collectives™ on Stack Overflow

How to add a key to a JSON array value?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related