exporting data from psql to json formatted wrong

Question

In my flask app I am trying to practice taking data from a psql table and putting it in a dropdown. I am using this to get the data with psycopg2 using a query from here:

def load_authors(self):
    # sql = "SELECT * FROM authors"
    sql = "select array_to_json(array_agg(row_to_json(t))) from (select id, name from authors) t"
    self.curs.execute(sql)
    data = self.curs.fetchall()
    print(data)
    return data

This is my data from psql that I want to format into json:

[(1, 'Christopher Paolini'), (2, 'Marie Lu'), (3, 'John Flanagan')]

The previous code exports the data into this:

[([{'id': 1, 'name': 'Christopher Paolini'}, {'id': 2, 'name': 'Marie Lu'}, {'id': 3, 'name': 'John Flanagan'}],)]

It seems to be taking the correctly formatted json that I want:

[{'id': 1, 'name': 'Christopher Paolini'}, {'id': 2, 'name': 'Marie Lu'}, {'id': 3, 'name': 'John Flanagan'}]

And putting it within another list? I don't have a very deep understanding of this yet so I was wondering why it is doing this and how to fix it. I have tried multiple different queries but so far they all return the list within a list.

score 1 · Answer 1 · answered Aug 18 '21 at 19:44

The quick answer is that using array_to_json will often result in nested arrays. Even the documentation illustrates that.

array_to_json('{{1,5},{99,100}}'::int[])

Yields

[[1,5],[99,100]]

Instead, here is an approach that will work a little better for you.

SELECT
  json_build_object(
    'authors',
    json_agg(authors)
  ) authors
from
  (
    select
      authors.id as id,
      authors.name as name
    from
      authors
  ) authors

This yields

{
  "authors": [
    {
      "id": 920,
      "name": "Ryan"
    },
    {
      "id": 3399,
      "name": "John"
    }
  ]
}

json_build_object allows you to build a custom object using alternating key/value pairs. Like this:

json_build_object('foo',1,'bar',2)

Which yields

{"foo": 1, "bar": 2}

In this case we're building an object that has a key of authors and a value of json_agg(authors) which will turn our authors query into an array of rows returned by the following aliased query.

It's a little hard to wrap the brain around, but it is very performant and I recommend this technique for grabbing a lot of JSON data out of the database quickly.

I do get that result, but it is nested within an outer array: [({'authors': [{'id': 1, 'name': 'Christopher Paolini'}, {'id': 2, 'name': 'Marie Lu'}, {'id': 3, 'name': 'John Flanagan'}]},)] I am not sure what is causing this outer array in all my queries — Evan, Aug 19 '21 at 15:13

Maurice Meyer · Accepted Answer · 2021-08-19T08:30:53.730

1

Usually row_to_json is sufficient:

cur = con.cursor()
query = '''
    select row_to_json(t) as obj from (
        select id, usage from products where id < 5
    ) t
'''

cur.execute(query)
data = cur.fetchall()
print([x[0] for x in data])
print([list(x[0].values()) for x in data])

Out:

[{'id': 2, 'usage': 'shop'}, {'id': 4, 'usage': 'shop'}]
[[2, 'shop'], [4, 'shop']]

edited Aug 19 '21 at 08:30

answered Aug 18 '21 at 19:59

Maurice Meyer

17,279
4
30
47

This is giving me a error: TypeError: 'NoneType' object is not iterable – Evan Aug 19 '21 at 15:17
`data` is empty ? – Maurice Meyer Aug 19 '21 at 15:26
That was my bad, my return statement was commented out. Your print([x[0] for x in data]) gives me the json format I want so I just used str([x[0] for x in data]) to assign it to my variable. Thank you! – Evan Aug 19 '21 at 15:47

exporting data from psql to json formatted wrong

2 Answers2