Is there any way to write this code avoiding intermediate steps/views in PostgreSQL coming from different tables?

Question

I am working in a large query I would like to eliminate intermediate steps, so I am trying to write the two queries below in just one.

The first query (QUERY 1) select the grid id from a table called tiles, from here i obtained an UUID who correspond to a value in a second table so to obtain the real value of this grid id I have to query a second query (QUERY 2). I have try to cast everything as I did in the third query for other values, but this approach doesn't work. Has someone an idea how can I manage to do this query (query 1 and 2) just in one:

QUERY 1:

SELECT
jsonb_array_elements(grid_id_tile.tiledata -> '34cfea5d-c2c0-11ea-9026-02e7594ce0a0'::text) ->> 'resourceId'::text AS grid_id
FROM mv_geojson_geoms mv
LEFT JOIN tiles grid_id_tile ON tv1.resourceinstanceid = grid_id_tile.resourceinstanceid
WHERE (( SELECT resource_instances.graphid
           FROM resource_instances
          WHERE mv.resourceinstanceid = resource_instances.resourceinstanceid)) = '34cfe98e-c2c0-11ea-9026-02e7594ce0a0'::uuid;

QUERY 2:

SELECT grid_id.legacyid AS grid_id,
FROM table 1 (Where I have obtained the grid id)
LEFT JOIN resource_instances grid_id ON hb1.grid_id = grid_id.resourceinstanceid::text

QUERY 3:

( SELECT "values".value
           FROM "values"
          WHERE ((name_ft_tile.tiledata ->> '34cfea97-c2c0-11ea-9026-02e7594ce0a0'::text)::uuid) = "values".valueid) AS nametype,

FROM mv_geojson_geoms mv

LEFT JOIN tiles name_ft_tile ON mv.resourceinstanceid = name_ft_tile.resourceinstanceid AND (name_ft_tile.tiledata ->> '34cfea97-c2c0-11ea-9026-02e7594ce0a0'::text) <> ''::text

WHERE (( SELECT resource_instances.graphid
           FROM resource_instances
          WHERE mv.resourceinstanceid = resource_instances.resourceinstanceid)) = '34cfe98e-c2c0-11ea-9026-02e7594ce0a0'::uuid

Those are the type of data I am managing at the moment:

This is the table tiles where in the jsonb got the UUID from the feature i would like to get the gridid

This is the table resource instance where the legacyid is

So from the query 1 I get this result Gridid is a UUID

And from query 2 I get this result with the grid_id code

This is what I obtain and I would like to get directly the grid_id value without intermediate steps

The third query is a sample of similar approach I did so in one query I get the value instead of the UUID, and it is what I would like to do with the grid_id.

But when I run the similar code I get the error, because I get the element from an array:

ERROR: cannot extract elements from a scalar CONTEXT: parallel worker SQL state: 22023

Please show us some **minimized** sample data and the expected output. Then we could have a look of what's possible. Currently we don't know anything about your table, your data and what you want to achieve — S-Man, Nov 27 '20 at 11:47
Everything you can do with a view, you can do with a single query. — Bergi, Nov 27 '20 at 12:06
I don't see the difference between query 1 and query 3. And where/how do you want to integrate query 2 into that? Can you please post how you'd do it with a view? — Bergi, Nov 27 '20 at 12:10

Bergi · Answer 1 · 2020-11-27T12:21:00.540

2

You can literally inline the query 1 as a subquery where you've written "table 1 (Where I have obtained the grid id)":

SELECT grid_id.legacyid AS grid_id
FROM (
   SELECT jsonb_array_elements(grid_id_tile.tiledata -> '34cfea5d-c2c0-11ea-9026-02e7594ce0a0'::text) ->> 'resourceId'::text AS grid_id
   FROM mv_geojson_geoms mv
   LEFT JOIN tiles grid_id_tile ON mv.resourceinstanceid = grid_id_tile.resourceinstanceid
   JOIN resource_instances ON mv.resourceinstanceid = resource_instances.resourceinstanceid
   WHERE resource_instances.graphid = '34cfe98e-c2c0-11ea-9026-02e7594ce0a0'::uuid;
) AS hb1
LEFT JOIN resource_instances grid_id ON hb1.grid_id = grid_id.resourceinstanceid::text;

edited Nov 27 '20 at 12:21

answered Nov 27 '20 at 12:15

Bergi

630,263
148
957
1,375

1

Thanks for all your answer, @Bergi I have tried similar approach before also i tried what you told but I get the same error. I have edited the question adding more details and the error I get. – gcj Nov 30 '20 at 09:55
1

That error seems to refer to `grid_id_tile.tiledata -> '34cfea5d-c2c0-11ea-9026-02e7594ce0a0'::text` not being an array afaics, but is not related to "*how to avoid intermediate steps*". Please [ask a new question](https://stackoverflow.com/questions/ask) with the exact query you are using, and table definitions and example data as text (not pictures!) so that one reproduce the issue e.g. on https://dbfiddle.uk/?rdbms=postgres_13. – Bergi Nov 30 '20 at 11:12
1

Hello @Bergi thanks for your help, how can I add some sample of the data without using pictures? I have tried but it ilooks very messed when you paste the data into the text editor. Coming back to the question, If understand correctly there is not way to use the same structure like in the QUERY 3, when you are using jsonb_array_element function? so directly in one query you can show, in my case, the GRID ID instead of first get the UUID (Query 1)and second the GRID ID (Query 2). – gcj Dec 01 '20 at 09:06
I mean post the data as an INSERT statement that can be used to set up a demo database. – Bergi Dec 01 '20 at 10:51

Is there any way to write this code avoiding intermediate steps/views in PostgreSQL coming from different tables?

1 Answers1