将JSON结果连接成单列PostgreSQL

Question

目前，我在表格中有两列，其中一列包含JSON文档，如下所示：

        CID:
        2
        Name:
        {"first" : "bob","1" : "william", "2" : "archebuster", "last" : "smith"}    

当我使用以下内容在此列上进行搜索时：

  SELECT "CID", "Name"->>(json_object_keys("Name")) AS name FROM "Clients" WHERE 
  "Name"->>'first' LIKE 'bob' GROUP BY "CID";

我得到：

  CID | name
--------------
  2   | bob
  2   | william
  2   | archebuster
  2   | smith

当我真正想要的是：

 CID | name
  2  | bob william archebuster smith

我该怎么做？我对postgresql中的JSON很陌生。 我尝试使用string_agg但没有成功，可能是因为我正在使用一个json列，尽管“->>”应该将结果转换为字符串类型。

更新：

- user3512424

---

不确定的话可以尝试使用 array_agg("Name"->>(json_object_keys("Name"))) AS name。 - Mihai

对于数组agg，我会得到“在无法接受set的上下文中调用了set-valued函数”的错误。对于字符串agg，我会得到“错误：string_agg（text）不存在”的错误。 - user3512424

将这些值插入临时表中，并在其上使用array_agg函数。 - Mihai

不太确定该怎么做，一直在尝试将其转换为数组，但遇到了同样的问题，每行都是{bob} {william}等。 - user3512424

请访问此处：http://dba.stackexchange.com/ - Mihai

Answer 1

首先，您需要理解，如果在SELECT子句中包含一个集合返回函数，则会创建一个隐式的LATERALCROSS JOIN。

事实上，您的查询看起来像这样：

SELECT "CID", "Name"->>"key" AS name
FROM "Clients"
CROSS JOIN LATERAL json_object_keys("Name") AS "foo"("key")
WHERE "Name"->>'first' LIKE 'bob'
GROUP BY "CID", "Name"->>"key"

如果你真的想这样做，你可以在这里应用一个聚合函数（可能是array_agg或string_agg）。 < p > SQLFiddle

- pozs

---

谢谢解释。但聚合函数在这里不起作用。这会导致数据库抱怨“在不能接受集合的上下文中调用了一个集合值函数调用”。 - user3512424

@user3512424 你错了，聚合函数将适用于这个翻译的例子。即 array_agg("Name"->>"key") AS names。除非你确切知道自己在做什么，否则你的查询就是一个不使用集合返回函数的完美例子。 - pozs

我不认为我是。请查看结果：http://imgur.com/8S4JAqq哦，等一下，我现在明白你的意思了...我会再试一次。 - user3512424