Postgis Query ST_Intersects with GeoJSON (jsonb)
<p>Given a PostgreSQL 9.4 jsonb column 'location' with content like this:</p>

<pre><code>{"type": "FeatureCollection","features": [
  {
    "properties": {},
    "geom": "01030000000100000004000000000000000000244000000000000049400000000000002840000000000000494000000000000026400000000000004A4000000000000024400000000000004940",
    "type": "Feature",
    "geometry": {"type": "Polygon", "coordinates": [[[10,50],[12,50],[11,52],[10,50]]]}             
  },
  {..},
  {..}
]}
</code></pre>

<p>To retrieve the feature key 'geom' I use this query:</p>

<pre><code>SELECT geom
FROM (
  SELECT jsonb_array_elements(t.location -&gt; 'features') -&gt; 'geom' AS geom
  FROM my_object t) AS g
WHERE geom IS NOT NULL;
</code></pre>

<p>Works. But now I would like to do a Postgis <code>ST_Intersects</code> query like this:</p>

<pre><code>SELECT ST_Intersects(g.geom, ST_GeomFromText('POINT(11 51)'))
FROM (
  SELECT t.iid, (jsonb_array_elements(t.location -&gt; 'features') -&gt; 'geom') AS geom
  FROM my_object t) AS g;
</code></pre>

<p>Doesn't work because g.geom is delivered as <code>jsonb</code>: <code>function st_intersects(jsonb, geometry) does not exist</code>. I tried casting to <code>text</code>, the error then is <code>function st_intersects(text, geometry) is not unique</code>. How I have to deal with <code>jsonb</code> results as input for Postgis functions?</p>