PostgreSQL Database Model

Question

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.

@Palec I cannot say that other question would really tell me much, maybe someone can answer this one with some direct example ? — Tomas
– Tomas, Commented Feb 8, 2014 at 3:48
Looks like simple Googling. hashrocket.com/blog/posts/… clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json slideshare.net/amdunstan/93json-26647827 dba.stackexchange.com/a/25251/30419 For all the JSON functions available, see the manual. — Palec
– Palec, Commented Feb 8, 2014 at 4:29
Palec's first link gives you everything you need. For my own curiosity I tried it out here — wwkudu
– wwkudu, Commented Feb 8, 2014 at 7:41
Yes, I spent a little more time reading all in that answer and basically it does answer everything (it does not work with arrays though, its just briefly mentioned). I have also asked because I want to know if you think doing it this way is a good idea. — Tomas
– Tomas, Commented Feb 8, 2014 at 7:55

harmic · Accepted Answer · 2014-02-08 07:25:24Z

3

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:

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.
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.

answered Feb 8, 2014 at 7:25

harmic

31k6 gold badges75 silver badges102 bronze badges

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

2 Comments

Tomas Over a year ago

Thank you for actually answering with nice code example. And yes it is little "fiddly", but I think it could be fine since I won't be calling this query everytime, but will save it as a view and just query that view. What do you think ?

Craig Ringer Over a year ago

@Tom I think it's down to personal preference. Do whatever you're more comfortable with. You should use a front-end server presenting a HTTP based API for your app anyway (rather than making PostgreSQL protocol connections over the Internet on flakey 3G) so whichever you chose, you can change later without having to alter your app.

Collectives™ on Stack Overflow

PostgreSQL Database Model

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related