如何将密钥添加到JSON数组值?

问题描述

我是Postgres的新手，正在使用9.4版.我有一个查询，返回一个json列.
如何将 key 添加到JSON数组 value 中?

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?

我的查询:

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;

输出类似于:

[{"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() (或jsonb_build_object())将键附加到您的值.
而更简单的 json_agg(t) (或jsonb_agg(t))而不是array_to_json(array_agg(t)):

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;

这篇关于如何将密钥添加到JSON数组值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！