o9s
is a microblog
home archives search feed blogroll


Postgres Spitting Mad JSON

I’m still learning how to use this feature. I love it. It’s an efficient way to generate JSON. It also allows you to have nested objects in your JSON.

I’ve read that it is up to 12 times faster than generating JSON from a Rails API.

Below is an SQL query I coded for a personal project.

SELECT
  posts.id,
  posts.title,
  posts.body,
  (
    SELECT row_to_json(wrap)
    FROM (
      SELECT
        users.id,
        users.display_name,
      FROM users
      WHERE posts.author_id = users.id
      LIMIT 1
    ) wrap
  ) as author
FROM posts

row_to_json is pure magic. The result of the query is the following.

[
  {
    "id": 1,
    "title": "My post title",
    "body": "My post body",
    "author": {
      "id": 1,
      "display_name": "o9s"
    }
  }
]

Amazing isn’t it?

Posted on 2018-03-01   #database     #postgres     #json  






← Next post    ·    Previous post →