From: Jesper Pedersen <jesper.pedersen@redhat.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: JSON path
Date: Thu, 14 Nov 2019 15:46:08 -0500
Message-ID: <17beac8b-e4e5-d37d-3412-3cbb2052090d@redhat.com> (raw)
In-Reply-To: <4339.1573754676@sss.pgh.pa.us>
References: <71a1d71e-5263-2980-117c-066a37e481bc@redhat.com>
<4339.1573754676@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jesper.pedersen@redhat.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: JSON path
In-Reply-To: <17beac8b-e4e5-d37d-3412-3cbb2052090d@redhat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox