Reply-To: jesper.pedersen@redhat.com
Subject: Re: JSON path
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
References: <71a1d71e-5263-2980-117c-066a37e481bc@redhat.com>
 <4339.1573754676@sss.pgh.pa.us>
From: Jesper Pedersen <jesper.pedersen@redhat.com>
Organization: Red Hat
Message-ID: <17beac8b-e4e5-d37d-3412-3cbb2052090d@redhat.com>
Date: Thu, 14 Nov 2019 15:46:08 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.1.1
MIME-Version: 1.0
In-Reply-To: <4339.1573754676@sss.pgh.pa.us>
Content-Language: en-US
Content-Type: text/plain; charset=WINDOWS-1252; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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.
>=20
> With
>=20
> 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 =3D 1 )
> select
>     col1,
>     col2,
>     jsondata->'val1'->'text()' as val1,
>     jsondata->'val2'->'text()' as val2,
>     jsondata->'val3'->'text()' as val3
> from foo;
>=20

Thanks Tom ! This works :)

I owe you one.

Best regards,
  Jesper