Duplicate JSON element in row_to_json() with self-join

Question

This is a follow-up to this excellent Q&A: 13227142.

I almost have to do the same thing (with the constraint of PostgreSQL 9.2) but I'm using only one table. Therefore the query uses a self-join (in order to produce the correct JSON format) which results in a duplicate id field. How can I avoid this?

Example:

CREATE TABLE books
(
  id serial primary key,
  isbn text,
  author text,
  title text,
  edition text,
  teaser text
);

SELECT row_to_json(row)
FROM
(
  SELECT id AS bookid,
  author,
  cover
  FROM books
  INNER JOIN 
  (
    SELECT id, title, edition, teaser
    FROM books
  ) cover(id, title, edition, teaser)
  USING (id)
) row;

Result:

{
  "bookid": 1,
  "author": "Bjarne Stroustrup",
  "cover": {
    "id": 1,
    "title": "Design and Evolution of C++",
    "edition": "1st edition",
    "teaser": "This book focuses on the principles, processes and decisions made during the development of the C++ programming language"
  }
}

I want to get rid of "id" in "cover".

Vao Tsun · Answer 1 · 2017-04-06T11:27:22.407

1

you need id to join, so without id you can't make such short query. You need to struct it. Smth like:

select row_to_json(row,true)
FROM
(
with a as (select id,isbn,author,row_to_json((title,edition,teaser)) r from books
)
select a.id AS bookid,a.author, concat('{"title":',r->'f1',',"edition":',r->'f2',',"teaser":',r->'f3','}')::json as cover
  from a
) row;
                      row_to_json
--------------------------------------------------------
 {"bookid":1,                                          +
  "author":"\"b\"",                                    +
  "cover":{"title":"c","edition":"d","teaser":"\"b\""}}
(1 row)

Also without join you use twice as less resources

edited Apr 06 '17 at 11:27

answered Apr 06 '17 at 09:34

Vao Tsun

47,234
13
100
132

It will fail if value contains special characters, like `"` – pumbo Apr 06 '17 at 10:51
added escaping to avoid it – Vao Tsun Apr 06 '17 at 10:59
1

Unfortunately, `to_json()` is available only as of version 9.3 – pumbo Apr 06 '17 at 11:00
Anyway, it's possible to implement your own text-escaping function. So it's a possible solution. – pumbo Apr 06 '17 at 11:06
1

used row_to_json for escaping – Vao Tsun Apr 06 '17 at 11:21
Wow, that was smart! – pumbo Apr 06 '17 at 11:24
Are the JSON operators `->`supported in 9.2? I only see them in the 9.3 docs. `ERROR: operator does not exist: json -> unknown` – Markus L Apr 06 '17 at 11:32
Thanks anyway, I can switch to this solution if we upgrade to 9.3... (ever *sigh*) – Markus L Apr 06 '17 at 12:09

pumbo · Accepted Answer · 2017-04-07T03:02:28.187

1

This turned out to be a tricky task. As far as I can see it's impossible to achieve with a simple query. One solution is to use a predefined data type:

CREATE TYPE bookcovertype AS (title text, edition text, teaser text);

SELECT row_to_json(row)
FROM
(
  SELECT books.id AS bookid, books.author, 
    row_to_json(row(books.title, books.edition, books.teaser)::bookcovertype) as cover
  FROM books
) row;

edited Apr 07 '17 at 03:02

answered Apr 06 '17 at 11:10

pumbo

3,646
2
25
27

This is solution is compatible to Postgres 9.2, thanks – Markus L Apr 06 '17 at 12:16
1

I looked at the query again and realized that `JOIN` is absolutely unnecessary here. I updated the query. – pumbo Apr 07 '17 at 03:04

Markus L · Answer 3 · 2017-04-06T11:36:08.350

For the sake of completeness I've stumbled upon another answer myself: The additional fields can be eliminated by string functions. However, I prefer AlexM's anwer because it will be faster and is still compatible with PostgreSQL 9.2.

SELECT regexp_replace(
  (
      SELECT row_to_json(row)
      FROM
      (
        SELECT id AS bookid,
        author,
        cover
        FROM books
        INNER JOIN 
        (
          SELECT id, title, edition, teaser
          FROM books
        ) cover(id, title, edition, teaser)
        USING (id)
      ) row
  )::text,
  '"id":\d+,',
  '')

Duplicate JSON element in row_to_json() with self-join

3 Answers3