sql - Postgres SQL 查询，将对关联数组中的字段进行分组

Question

示例:我有表“tickets”

id, int
client_id, int
client_name, text

我需要的不是通常的 select ("SELECT id, client_id, client_name FROM tickets") 结果:

{
    "id": 2,
    "client": {
        "id": 31,
        "name": "Mark"
    }
}

Answer 1

如果你喜欢为此使用 SQL，有 json_build_object function :

SELECT
  json_build_object(
    'id', id,
    'client', json_build_object(
      'id', client_id,
      'name', client_name))
FROM
  tickets;

例子:

#!/usr/bin/env python

import psycopg2
import json

conn = psycopg2.connect('')
cur = conn.cursor()
cur.execute("""
    with tickets(id, client_id, client_name) as (values(1,2,'x'),(3,4,'y'))
    SELECT
      json_build_object(
        'id', id,
        'client', json_build_object(
          'id', client_id,
          'name', client_name))
    FROM
      tickets;
    """)

for row in cur.fetchall():
    print row, json.dumps(row[0])

输出:

({u'client': {u'id': 2, u'name': u'x'}, u'id': 1},) {"client": {"id": 2, "name": "x"}, "id": 1}({u'client': {u'id': 4, u'name': u'y'}, u'id': 3},) {"client": {"id": 4, "name": "y"}, "id": 3}