PostgreSQL/PostGIS Geom to JSON with Inner Join to another table

Question

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?

• Jakub Kania: You should write what is wrong with the query you tried. Syntax error? Wrong result?
• Jakub Kania: And what's more (because your query is a certain syntax error), where did you try to include those fields?
• Mark Cupitt: Good point, Question Edited
• Jakub Kania: I'm still not sure how you want the resultset to look so can't fully correct it but I posted a few hints.
• Mark Cupitt: I have spent the day trying, added an update, in this case, I need multiple rows returned. The projectsgeom table has multiple rows that I want to join with the columns contained in table activation id as properties

Answer 1

You're getting an error because:

• activations.activationid is in a subquery that's been aliased to zz. Since the subquery doesn't really do anything you can flatten it and get

  INNER JOIN hat.activations.activationid zz ON (zz.activationid = ...

• However projectsgeom is on a diffrent nesting level too so the above will only change the syntax error. Either you'll have to move the join into subquery f or select projectsgeom.activationid out of f and fc.

• Mark Cupitt: What I am trying to achieve is to get ALL the columns from the hat.activations table in the properties of the second row_ro_json
• Mark Cupitt: I have spent the day trying, added an update, in this case, I need multiple rows returned. The projectsgeom table has multiple rows that I want to join with the columns contained in table activation id as properties

Answer 2

I was able to create the correct answer, so have posted the correct Query here for future users who want to convert GIS data from two joined PostGres Table's to geoJSON

I used information from the following pages to help me solve this:

http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql http://bender.io/2013/09/22/returning-hierarchical-data-in-a-single-sql-query/ https://stackoverflow.com/questions/21137237/postgres-nested-json-array-using-row-to-json https://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins

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
 p.*
 FROM hat.activations p
 WHERE p.activationid= lg.activationid
 ) As l
 )
 ) As properties , lg.agency, lg.activationid, lg.projectid, lg.taskid, lg.extent, lg.updated, lg.created
 FROM hat.projectsgeom As lg 
 WHERE activationid = 'HOT-0001' AND agency = 'HOT' AND type = 'activation'
 ) As f 
) As fc;