如何将密钥添加到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;