arches_geojson_select.sql

-- insert into tiles(tiledata, nodegroupid, resourceinstanceid)
--     values ('{
--     "2d18cb13-d175-47d6-b218-8f2644cf8b3c": { 
--         "type": "Point",
--         "coordinates": [100.0, 0.0] 
--    }
-- }','2d18cb13-d175-47d6-b218-8f2644cf8b3c', '40000000-0000-0000-0000-000000000000');

select 
    t.tileid,
    t.resourceinstanceid, 
    n.nodeid, 
    st_geomfromgeojson(
        cast(
            t.tiledata::json->cast(
                n.nodeid as text
            ) as text
        )
    ) as geom 
    from tiles t
    left join public.nodes n
        on t.nodegroupid = n.nodegroupid
    where (
        select count(*)
        from jsonb_object_keys(t.tiledata)
        where jsonb_object_keys in (
            select cast(nodeid as text)
                from nodes n
                where datatype='geometry'
        )
    ) > 0
    and n.datatype = 'geometry';