TheVanillaCoke TheVanillaCoke - 3 months ago 14
SQL Question

Multiple json_object_agg functions in subquery return syntax error

I have two tables

CREATE TABLE service (
id SERIAL PRIMARY KEY,
image VARCHAR
);

CREATE TABLE service_description (
id SERIAL PRIMARY KEY,
service_id INTEGER,
language_id INTEGER,
title VARCHAR,
subtitle VARCHAR,
col_left TEXT,
col_right TEXT
);


I'm trying to retrieve the last 4 columns in the second table as objects with language_id as key.

The following query works (ommited col_left/right to save space)

SELECT
s.*,
(
SELECT
json_object_agg(language_id, title) AS title
FROM
service_description sd
),
(
SELECT
json_object_agg(language_id, subtitle) AS subtitle
FROM
service_description sd
)

FROM
service s


Though when I do

SELECT
s.*,
(
SELECT
json_object_agg(language_id, title) AS title,
json_object_agg(language_id, subtitle) AS subtitle
FROM
service_description sd
)

FROM
service s


I get err code 42601 - syntax error. What's wrong?

Answer

The problem in your query is you are selecting more than one column inside the sub-query. Try something like this

SELECT * 
FROM   service s 
       CROSS JOIN (SELECT Json_object_agg(language_id, title)    AS title, 
                          Json_object_agg(language_id, subtitle) AS subtitle 
                   FROM   service_description sd) B 
Comments