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?

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.

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;