1

I recently encountered this example (via a tutorial and other answer):

 SELECT row_to_json(x)
 FROM (SELECT id, title FROM my_table) x;

I'm confused about the use of row_to_json(x). I thought it only accepted rows, but it seems to also accept a sub-query. I found this counter-intuitive. According to the documentation, it accepts a record: row_to_json(record [, pretty_bool]). Other examples online use a row() constructor: row_to_json(row(col1, col2)). Does this mean a sub-query is the same as a record and a row value/composite value?

0

Given next example:

CREATE TABLE tbl (id int, title text);

INSERT INTO tbl VALUES
(1, 'Title 1'),
(2, 'Title 2'),
(3, 'Title 3');

Does this mean a sub-query is the same as a record and a row value/composite value?

Let me select some records from tbl:

SELECT id, title FROM tbl;

id | title  
-: | :------
 1 | Title 1
 2 | Title 2
 3 | Title 3

Now using a subquery:

SELECT * FROM (SELECT id, title FROM tbl) t;

id | title  
-: | :------
 1 | Title 1
 2 | Title 2
 3 | Title 3

As you can see both queries return same rows, then you can use both queries to generate a JSON result.

SELECT row_to_json(x) 
FROM   (SELECT id, title FROM tbl) x;
| row_to_json                |
| :------------------------- |
| {"id":1,"title":"Title 1"} |
| {"id":2,"title":"Title 2"} |
| {"id":3,"title":"Title 3"} |

dbfiddle here

As @ypercube has pointed out, when you executes:

SELECT tbl FROM tbl;

you get this result:

| tbl           |
| :------------ |
| (1,"Title 1") |
| (2,"Title 2") |
| (3,"Title 3") |

same output as using:

SELECT ROW(1, 'Title 1');
| row           |
| :------------ |
| (1,"Title 1") |

It is a (named) composite type. (Have a look at docs about TYPE)

By default, the value created by a ROW expression is of an anonymous record type. If necessary, it can be cast to a named composite type — either the row type of a table, or a composite type created with CREATE TYPE AS

Having a look at row_to_json() function:

+------------------------+----------------------------------------------------+---------------------------+---------------------+
| row_to_json(record [,  | Returns the row as a JSON object.                  | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
|          pretty_bool]) | Line feeds will be added between level-1 elements  |                           |                     |
|                        | if pretty_bool is true.                            |                           |                     |
+------------------------+----------------------------------------------------+---------------------------+---------------------+

Uses a row as example: row_to_json(row(1,'foo'))

  • Besides SELECT id, title FROM tbl;, you could also show the output of SELECT tbl FROM tbl;. (and see my comment in the question and the linked one) – ypercubeᵀᴹ Mar 24 '18 at 17:55
  • @McNets Thanks for taking the time out and explaining. It was very clear and removed a lot of my misconceptions. – da99 Mar 24 '18 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.