将JSON结果连接到单列postgresql

So, at the moment I have two columns in a table, one of which containing a JSON document, like so:

所以,目前我在表中有两列,其中一列包含JSON文档,如下所示:

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

When I do a search on this column using:

当我使用以下方法搜索此列时:

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

I get:

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

When really I want:

我真的想要的时候:

 CID | name
  2  | bob william archebuster smith

How would i go about doing this? I'm new to JSON in postgresql. I've tried string_agg and it wouldn't work, presumably because i'm working in a json column, despite the fact '->>' should type set the result to string

我该怎么做呢?我是postgresql中的JSON新手。我已经尝试过string_agg并且它不起作用,大概是因为我在json列中工作,尽管' - >>'应该键入将结果设置为字符串

UPDATE:

1 个解决方案

#1

First, you need to understand, if you include a set-returning function into the SELECT clause, you will create an implicit LATERAL CROSS JOIN.

首先,您需要了解,如果在SELECT子句中包含set-returns函数,则将创建一个隐式的LATERAL CROSS JOIN。

Your query in reality looks like this:

您的实际查询如下所示:

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"

If you really want to do that, you can apply an aggregate function here (possibly array_agg or string_agg).

如果你真的想这样做,你可以在这里应用一个聚合函数(可能是array_agg或string_agg)。

SQLFiddle

#1