Postgres SQL query, that will group fields in associative array

Question

Example: I have table "tickets"

id, int
client_id, int
client_name, text

Instead of the usual select ("SELECT id, client_id, client_name FROM tickets") I need something that will give as result:

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

Abelisto · Accepted Answer · 2017-04-06 17:34:56Z

2

If you like to use SQL for this, there is json_build_object function:

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

Example:

#!/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])

Output:

({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}

edited Apr 6, 2017 at 17:34

answered Apr 6, 2017 at 16:47

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ildar Akhmetzyanov Over a year ago

It works, but every result in "json_build_object". Looks like: { "json_build_object": {id: ...} },{ "json_build_object": {id: ...} }. Can I put data level up?

Ildar Akhmetzyanov Over a year ago

Without "json_build_object" key

Abelisto Over a year ago

@IldarAkhmetzyanov Probably we have some differences in code. Added example.

Ildar Akhmetzyanov Over a year ago

Problem with settings: cursor_factory=RealDictCursor.

Collectives™ on Stack Overflow

Postgres SQL query, that will group fields in associative array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related