在 SQL (Postgres) 中作为 JSON 对象数组返回

本文介绍了在 SQL (Postgres) 中作为 JSON 对象数组返回的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有下表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_two 分组的对象数组 { value_three, value_four }.value_two 应该单独出现在结果中.结果应该是这样的:

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?

`json_build_object()` Postgres 9.4 或更新版本

或者 jsonb_build_object() 返回jsonb.

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 对象.按照惯例，参数列表由交替的键和值组成.

对于任何版本(包括 Postgres 9.3)

row_to_json() 带有 ROW 表达式可以解决问题:

`For any version (incl. Postgres 9.3)`

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;

但是您丢失了原始列名.转换为已注册的行类型可以避免这种情况.(临时表的行类型也用于临时查询.)

But you lose original column names. A cast to a registered row type avoids that. (The row type of a temporary table serves for ad hoc queries, too.)

CREATE TYPE foo AS (value_three text, value_four text);  -- once in the same session

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

或者使用 subselect 代替 ROW 表达式.更详细，但没有类型转换:

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) t))) AS value_four
FROM   mytable
GROUP  BY value_two;

克雷格相关回答中的更多解释:

More explanation in Craig's related answer:

具有嵌套连接的 PostgreSQL 9.2 row_to_json()

db<>fiddle 这里

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

subselect