json - PostgreSQL。创建嵌套的 json 对象

Question

在我的 postgres 函数中，我有一组 json:

{"layer_name":"rooms","properties":{"fillOpacity":1,"strokeColor":"#606576","strokeWidth":"3"}}
{"layer_name":"map","properties":{"fillColor":"#3D404F"}}

我需要将其修改为一个结构:

{
  "rooms": {"fillOpacity":1,"strokeColor":"#606576","strokeWidth":"3"}},
  "map: {"fillColor":"#3D404F"}
}

我尝试遍历集合并填充两个数组(一个用于键，另一个用于值)所以我可以使用 json_object(keys text[], values text[]) 来创建需要的 json:

CREATE OR REPLACE FUNCTION appearance.get_styles(out response text)
  RETURNS text
AS
$BODY$
  declare
    vResult json;
    i json;
    keys TEXT[];
    vals TEXT[];
    layers json;
begin
  with a as (SELECT layer_name, properties FROM appearance.styles)
  select json_agg(a)
    into vResult
    from a;

  FOR i IN SELECT * FROM json_array_elements(vResult)  
  LOOP
    RAISE NOTICE 'props %', i;
    keys = array_append(keys, i->>'layer_name');
    vals = array_append(vals, i->>'properties');

  END LOOP;
    layers = json_object(keys, vals);
    RAISE NOTICE 'props %', layers;

 response := concat ('"layersStyles"', ':', coalesce(layers::text,'[]'));
 response := '{' || response || '}';

end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

但问题是我必须将 json 转换为 text，最后我得到转义字符串作为值:

"map" : "{\"fillColor\":\"#3D404F\"}", "rooms" : "{\"fillOpacity\":1,\"strokeColor\":\"#606576\",\"strokeWidth\":\"3\"}"}

那不是我需要的。我不知道如何制作嵌套的 json 对象。看不到它的任何功能 here .有更好的方法吗？

Answer 1

借助 json_object_agg 函数解决。

CREATE OR REPLACE FUNCTION appearance.get_styles(out response text)
  RETURNS text
AS
$BODY$
  declare
    vResult json;    
begin  
  SELECT json_object_agg("layer_name", "properties") from appearance.styles
    into vResult;  
  response := concat ('"layersStyles"', ':', coalesce(vResult::text,'[]'));
  response := '{' || response || '}';

end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;