PostgreSQL specifying a JSON field as other JSON

Question

I want to get json out of my PostgreSQL database running version:

PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit

The data I am trying to get is like:

{
  "a" : "value",
  "b" : {
        "c" : "some_vaue here"
   }
}

I am getting value from one table and some_value from some other table using join.

How can I achieve that?

Here is something similar, but I'm getting an error:

QUERY="
    SELECT row_to_json(o) FROM (
      SELECT oltl.id::text as ordinal, 
             oltl.nid as code,
             oltl.description as description, 
             SELECT row_to_json(j) 
               FROM ( SELECT cilantag.tag as code ) AS j
        from olt_languages oltl 
               inner join ci_language_tags cilantag 
                       on oltl.ci_language_tag_id=cilantag.id
    ) AS o";

The error I am getting is

ERROR: syntax error at or near "SELECT" LINE 1: ...oltl.nid as code,oltl.description as description, SELECT row... ^ ` enter code here

klin · Accepted Answer · 2016-07-16 21:27:04Z

1

A subquery in a select list has to be enclosed in brackets, try:

SELECT row_to_json(o) 
FROM (
    SELECT 
        oltl.id::text AS ordinal, 
        oltl.nid AS code,
        oltl.description AS description, 
        (           -- added
            SELECT row_to_json(j)
            FROM (
                SELECT cilantag.tag AS code 
                ) AS j
        ) AS tags   -- + alias
    FROM olt_languages oltl 
    INNER JOIN ci_language_tags cilantag 
    ON oltl.ci_language_tag_id=cilantag.id
) AS o

answered Jul 16, 2016 at 21:27

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL specifying a JSON field as other JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related