返回在SQL JSON对象数组（Postgres的） [英] Return as array of JSON objects in SQL (Postgres)

问题描述

我有以下表 MyTable的：

 id │ value_two │ value_three │ value_four 
────┼───────────┼─────────────┼────────────
  1 │ a         │ A           │ AA
  2 │ a         │ A2          │ AA2
  3 │ b         │ A3          │ AA3
  4 │ a         │ A4          │ AA4
  5 │ b         │ A5          │ AA5

我要查询对象的数组 {value_three，value_four} 按 value_two 分组。 value_two 应该是在结果上自己的present。其结果应该是这样的：

I want to query an array of objects { value_three, value_four } grouped by value_two. value_two should be present on its own in the result. The result should look like this:

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

不要紧，它是否使用 json_agg（）或 ARRAY_AGG（）。

不过，我所能做的最好的是：

However the best I can do is:

with MyCTE as ( select value_two, value_three, value_four from MyTable ) select value_two, json_agg(row_to_json(MyCTE)) value_four from MyCTE group by value_two;

将返回：

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_two":"a","value_three":"A","value_four":"AA"}, {"value_two":"a","value_three":"A2","value_four":"AA2"}, {"value_two":"a","value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_two":"b","value_three":"A3","value_four":"AA3"}, {"value_two":"b","value_three":"A5","value_four":"AA5"}]

随着的对象，我想摆脱额外的 value_two 键。其中SQL（Postgres的）查询我应该使用？

With an extra value_two key in the objects, which I would like to get rid of. Which SQL (Postgres) query should I use?

推荐答案

<一个href=\"http://www.postgresql.org/docs/9.4/interactive/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE\"><$c$c>row_to_json()以<一个href=\"http://www.postgresql.org/docs/current/interactive/sql-ex$p$pssions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\"><$c$c>ROW前pression会做的伎俩：

row_to_json() with a ROW expression would do the trick:

SELECT value_two
     , json_agg(row_to_json((value_three, value_four))) AS value_four
FROM   MyTable 
GROUP  BY value_two;

您宽松的原始列名，但。铸造一个知名的复合类型会避免这种情况。 （行类型温度表供应，太！）

You loose original column names, though. Casting to a well-known composite type would avoid that. (The row type of a TEMP TABLE serves, too!)

CREATE TYPE foo AS (value_three text, value_four text);  -- once

SELECT value_two
     , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM   tbl
GROUP  BY value_two;

或者使用再选择而不是行前pression。更详细的，但没有类型转换：

Or use a subselect instead of the ROW expression. More verbose, but without type cast:

SELECT value_two
     , json_agg(row_to_json(
          (SELECT t FROM (SELECT value_three, value_four) 
                           AS t(value_three, value_four))
       )) AS value_four
FROM   tbl
GROUP  BY value_two;

替代语法：

(SELECT t FROM (SELECT value_three AS value_three, value_four AS value_four) t)

在克雷格的相关答案更多的解释：

More explanation in Craig's related answer:

• PostgreSQL 9.2 row_to_json（）与嵌套加入

• PostgreSQL 9.2 row_to_json() with nested joins

SQL小提琴。

您的爱的在即将到来的9.4版本，这个新功能。然后，它的工作原理是：

You will love this new function in the upcoming version 9.4. Then it works like:

SELECT value_two, json_agg(
        json_build_object('value_three', value_three
                       , 'value_four' , value_four)
               ) AS value_four
FROM   MyTable 
GROUP  BY value_two;

引述发行说明：

  

• 添加新的JSON函数允许任意复杂JSON树（安德鲁·邓斯坦，劳伦斯·罗）建设

• Add new JSON functions to allow for the construction of arbitrarily complex JSON trees (Andrew Dunstan, Laurence Rowe)

新功能包括： json_array_elements_text（）， json_build_array（），
   JSON_OBJECT（）， json_object_agg（）， json_to_record（）和 json_to_recordset（）。

New functions include json_array_elements_text(), json_build_array(), json_object(), json_object_agg(), json_to_record(), and json_to_recordset().

这篇关于返回在SQL JSON对象数组（Postgres的）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！