Re: JSON path

[Jesper Pedersen]

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