I am currently producing JSON from a database successfully using the following query:
SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry,
row_to_json((SELECT l FROM (SELECT extent, activationid) As l)) As properties FROM hat.projectsgeom As lg
WHERE activationid = 'HOT-0001' AND agency = 'HOT' AND type = 'activation') As f ) As fc;
However, I am stuck when I try and use INNER JOIN
to another table.
This is my attempt so far ..
ERROR: relation "activations.activationid" does not exist
LINE 5: INNER JOIN ( select * from hat.activations.activationid) zz ...
SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry,
row_to_json((SELECT l FROM (SELECT extent, activationid) As l)) As properties FROM hat.projectsgeom As lg
WHERE activationid = 'HOT-0001' AND agency = 'HOT' AND type = 'activation') As f ) As fc
INNER JOIN ( select * from hat.activations.activationid) zz ON (hat.activations.activationid = hat.projectsgeom.activationid );
I have tried a number of variations, I think this is close, but I cannot get a result ..
What do I need to change in the second query to get the Inner Join to work properly and return all fields form the second table?
UPDATE:
Based on Jakub's response, I have been able to get closer.
SELECT row_to_json(fc) FROM
(
SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM
(
SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json
(
(
SELECT l FROM
(
SELECT *
FROM hat.projectsgeom
INNER JOIN hat.activations ON hat.projectsgeom.activationid = hat.activations.activationid
WHERE hat.projectsgeom.agency = 'HOT' AND hat. projectsgeom.type = 'activation'
) As l
)
) As properties FROM hat.projectsgeom As lg
) As f
) As fc;
Is returning the following error
ERROR: more than one row returned by a subquery used as an expression
I am baffled on how to get this to work, with the Json.
The table projectsgeom has multiple rows that need to be INNER JOINED to table activationid and all the columns from both tables returned as the properties, using the WHERE Clause on table projectsgeom
What Query will work?