Raw SQL queries in a REST Api

Question

Say I have these 2 tables:

posts

 id | user_id | title | slug | content | views
----+---------+-------+------+---------+-------

users

 id | username | email | hash | role | auth 
----+----------+-------+------+------+------

With these two tables I want to do a SELECT * FROM posts ... query and ultimately end up responding with something like this:

{[
  {
    "id": "1",
    "user_id": "2",
    "title": "Foo",
    "slug": "foo",
    "content": "bar",
    "views": "0",
    "user": {
      "id": "2",
      "username": "john",
      "email": "john@smith.com",
      "hash": "aeigh*£HAEGhoiaehg",
      "role": "admin",
      "auth": "aeg89hiae9g8hA*H£GA(*EHG"
    }
  }
]}

I'm pretty new to SQL, and patterns like this seem pretty common when using an ORM, so I'm wondering what steps I have to do to get a result like this using raw SQL.

Any idea?

crthompson · Accepted Answer · 2014-12-16T21:49:01.520

2

In postgresql 9.2 or greater you can use the row_to_json function:

select row_to_json(posts) 
from 
  posts....;

Your query might look something like:

select row_to_json(row)
from (
    select p.*, urd AS user
    from posts p
    inner join (
        select ur.*
        from users ur
    ) urd(id,username,email,hash,role,auth) on urd.id = p.user_id 
) row;

The straight sql for this wouldnt need to be so complicated:

select
  p.*, u.*
from
  posts p
  inner join users u on p.user_id = u.id

edited Dec 16 '14 at 21:49

answered Dec 16 '14 at 19:32

crthompson

15,653
6
58
80

Seems interesting. Although, how will that help me in regards to joining data? I probably should have been a little more specific in my needs, but I'm more concerned about that than converting to JSON. – daryl Dec 16 '14 at 19:35
@daryl not sure if my syntax is perfect, but its possible to join data to get what you need. The recursive `urd` method there was cobbled together from http://stackoverflow.com/a/13227451/2589202 – crthompson Dec 16 '14 at 19:40
Hey, just wondering, is it possible to do this without returning it as JSON? (I'll convert it later) – daryl Dec 16 '14 at 21:01
@daryl json his a hierarchical format, where as sql is not. So data would be duplicated for `p` but give you unique `u`. Hope that makes sense. – crthompson Dec 16 '14 at 21:51
Yeah, similar to that, any idea how I would manage that response in code so I can extract the user columns? – daryl Dec 16 '14 at 22:09
@daryl, I suppose it depends on which language you are writing in. Plenty of stuff out there for every language – crthompson Dec 16 '14 at 22:28

Erwin Brandstetter · Answer 2 · 2020-07-30T22:44:41.377

what steps I have to do to get a result like this using raw SQL.

row_to_json() has a second parameter to pretty-print the outer level. The manual:

Line feeds will be added between level 1 elements if pretty_bool is true.

Use a subquery to add the user to the row:

SELECT row_to_json(combi, true) AS pretty_json
FROM  (
   SELECT p.*, u AS user  --  whole users row
   FROM   posts p
   JOIN   users u ON u.id = p.user_id
   WHERE  p.id = 1
   ) combi;

You only need one subquery level.

Or use jsonb_pretty() in Postgres 9.4 or later to pretty-print all levels:

SELECT jsonb_pretty(to_jsonb(combi)) AS pretty_jsonb
FROM  (
   SELECT p.*, u AS user
   FROM   posts p
   JOIN   users u ON u.id = p.user_id
   WHERE  p.id = 1
   ) combi;

Keys (former column names) are ordered alphabetically now, that's how they are stored in jsonb.

db<>fiddle here
_{OLD sqlfiddle - cast to text (::text) for display; line feeds not displayed}

Mark Sawers · Answer 3 · 2017-04-01T23:47:40.373

0

Alternatively you could use a thin middle tier - restSQL. It's an open-source, ultra-lightweight persistence layer, with out of the box support for PostgreSQL. It's tested with 9.3 but 9.2 is likely not an issue.

You can deploy in about 10 minutes using the docker image.

edited Apr 01 '17 at 23:47

answered Apr 01 '17 at 23:31

Mark Sawers

302
3
7

Raw SQL queries in a REST Api

posts

users

3 Answers3