CODEWITHSUNDEEP
sqljsonpostgresql
Tomas
Tomas

Reputation: 2726

PostgreSQL Database Model

I have switched to PostgreSQL for my mobile app backend and trying to figure out best way to store and query data. The thing is that storing the data is best as SQL, but retrieving data would be best as document.

So for example, I have table Items:

+----+--------+------+
| id | title  | uuid | 
+----+--------+------+
|  1 | Hello  |  32  |
|  2 | World  |  25  |
|  3 | Tom    |  435 |
+----+--------+------+

And then table Records:

+----+---------+----------+
| id | itemId  | resource | 
+----+---------+----------+
|  1 | 1       |  res1    |
|  2 | 1       |  res2    |
|  3 | 1       |  res3    |
|  4 | 2       |  res4    |
+----+---------+----------+

Which is pretty much standard SQL approach. Now what I want to get is this:

{
  id: 1,
  title: "Hello",
  uuid: 32,
  records: [
    {id: 1, resource: "res1"},
    {id: 2, resource: "res2"}, 
    {id: 3, resource: "res3"}
  ]
}

I think you get the picture. I am fairly new to PostgreSQL and I am sure that in all its awesomeness there will be elegant solution to this. All I could think of was creating view table that I could query, but not sure how exactly build the query for this.

Upvotes: 0

Views: 308

Answers (1)

harmic
harmic

Reputation: 30577

If you have a set of tables you are going to query, and you want the output back as a JSON data structure, you now have two choices:

  1. Execute the query, and transform the result to JSON in your application backend. This is a fairly standard approach and is probably still the simplest, especially if the language you are coding your backend in has good JSON support.

  2. Structure the query so that it returns a result encoded in JSON, which you can do thanks to PostgreSQL 9.2 and later.

This article gives a good introduction to the latter approach. Here is a query which gives you what you requested above:

select row_to_json(t)
from (
  select items.id,
    items.title,
    items.uuid,
    (
        select array_to_json(array_agg(row_to_json(d)))
        from (
            select records.id,
                records.resource
            from records
            where items.id=records.itemid
        ) d
    ) as records
  from items
  where items.id=1
) as t;

Result:

{
  "id": 1,
  "title": "Hello",
  "uuid": "32",
  "records": [
    {
      "id": 1,
      "resource": "res1"
    },
    {
      "id": 2,
      "resource": "res2"
    },
    {
      "id": 3,
      "resource": "res3"
    }
  ]
}

I used jsonprettyprint.com to make it look nicer - it actually comes out as a single line with no indenting, but still quite valid.

Creating JSON output this way is fiddly, at least for my tastes. I'd probably prefer to do it in the application. But as the JSON support matures I expect it will get easier.

Upvotes: 3

Related Questions