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: possible duplicate of JSON output in Postgresql
• Tomas: @Palec I cannot say that other question would really tell me much, maybe someone can answer this one with some direct example ?
• Palec: 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.
• wwkudu: Palec's first link gives you everything you need. For my own curiosity I tried it out here
• Tomas: 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.

Answer 1

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.

• Tomas: 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: @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.