Re: JSON path

Hi,

On 11/14/19 1:04 PM, Tom Lane wrote:
> As of v12, that WITH will get flattened, so that you still end up
> with three invocations of jsonb_path_query_first, as EXPLAIN VERBOSE
> will show you.  You could write "WITH foo AS MATERIALIZED ..." to
> prevent that, but then you'll need to stick the WHERE clause inside
> the WITH or you'll end up running jsonb_path_query_first for every
> row of tbl.
>
> With
>
> explain verbose WITH foo AS materialized (select
>     id,
>     col1,
>     col2,
> jsonb_path_query_first(data,'$.lvl1.lvl2.lvl3.lvl4.lvl5') as jsondata
>     from tbl where id = 1 )
> select
>     col1,
>     col2,
>     jsondata->'val1'->'text()' as val1,
>     jsondata->'val2'->'text()' as val2,
>     jsondata->'val3'->'text()' as val3
> from foo;
>

Thanks Tom ! This works :)

I owe you one.

Best regards,
  Jesper