Re: Trouble matching a nested value in JSONB entries

2018-07-03 Thread Oleg Bartunov
On Wed, Jun 20, 2018 at 10:06 PM, Enrico Thierbach  wrote:
> Hi list,
>
> I have some trouble matching a value in a JSONB object against multiple
> potential matches.
>
> Lets say, I have a table with an id, and a metadata JSONB column, which
> holds data like the following
>
> 1 | {"group_id": 1}
> 2 | {“group_id": 1}
> 3 | {“group_id": 2}
> 4 | {“group_id": 3}
>
> I would like to run a query which gives me the result of SELECT id FROM
> mytable WHERE metadata->>’group_id’ IN (1,2). Now, obviously I could use
> this query, but I
> would like to get away without an explicit index on metadata->>’group_id’,
> and I was hoping to find something using the JSONB containment operators,
> with support
> of a gist or gin index.
>
> The following seems to work
>
> select * from mytable where (metadata @> '{"group_id":1}')
>
> but only with a single value to match.
>
> I could, of course, also “denormalize” the query a la
>
> select * from mytable where (metadata @> '{"group_id":1}') OR (metadata @>
> '{"group_id”:2}’)
>
> but this seems to call for long execution times; also, depending on the
> number of different tag names and values to match this could really explode
> into quite a
> large query.
>
> Stackoverflow suggests the use of ANY
>
> select * from mytable where (tags->'group_id' @> ANY( ARRAY
> ['1','3']::jsonb[] ) );
>
> https://dba.stackexchange.com/questions/130699/postgresql-json-query-array-against-multiple-values
>
> This seems to work - but doesn’t that require a group_id specific index
> again?
>
> Anything I overlooked?
>
> Best,
> /eno
>
> PS: Please note that I am currently at postgres 9.5. An update, if
> necessary, would be possible though.

Upgrade, please !

I have only master 11beta2 right now:

select * from qq  where js @> '{"group_id":1}';
 id |   js
+-
  1 | {"group_id": 1}
  2 | {"group_id": 1}
(2 rows)


>
> --
> me at github: https://github.com/radiospiel
> me at linked.in: https://www.linkedin.com/in/radiospiel



-- 
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



Re: tsvector not giving expected results on one host

2022-12-21 Thread Oleg Bartunov
Dan,

it's always good to specify configuration name in a query to avoid
recheck,  since
websearch_to_tsquery(regconfig, text) is immutable, while
websearch_to_tsquery(text) is stable.

See the difference:

[local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
from apod where  websearch_to_tsquery('simple','galaxies') @@ fts;
  QUERY PLAN
---
 Bitmap Heap Scan on apod (actual time=0.008..0.008 rows=0 loops=1)
   Recheck Cond: ('''galaxies'''::tsquery @@ fts)
   ->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.007..0.007
rows=0 loops=1)
 Index Cond: (fts @@ '''galaxies'''::tsquery)
 Planning Time: 0.134 ms
 Execution Time: 0.022 ms
(6 rows)

Time: 0.369 ms
[local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
from apod where  websearch_to_tsquery('galaxies') @@ fts;
   QUERY PLAN
-
 Bitmap Heap Scan on apod (actual time=0.107..1.463 rows=493 loops=1)
   Filter: (websearch_to_tsquery('galaxies'::text) @@ fts)
   Heap Blocks: exact=276
   ->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.059..0.059
rows=493 loops=1)
 Index Cond: (fts @@ websearch_to_tsquery('galaxies'::text))
 Planning Time: 0.125 ms
 Execution Time: 1.518 ms
(7 rows)

On Sat, Dec 17, 2022 at 11:34 PM Dan Langille  wrote:
>
> On Sat, Dec 17, 2022, at 3:22 PM, Dan Langille wrote:
> > On Sat, Dec 17, 2022, at 3:14 PM, Dan Langille wrote:
> >> On Sat, Dec 17, 2022, at 2:55 PM, Tom Lane wrote:
> >>> "Dan Langille"  writes:
>   pkgmessage_textsearchable2 | tsvector |   | 
>   | generated always as (to_tsvector('english'::regconfig, 
>  translate(pkgmessage, '/'::text, ' '::text))) stored
> >>>
> >>> That is not likely to play well with this:
> >>>
>  freshports.org=> show  default_text_search_config ;
>   default_text_search_config
>  
>   pg_catalog.simple
> >>>
> >>> because "english" and "simple" will stem words differently.
> >>>
> >>> regression=# select websearch_to_tsquery('english', 'example');
> >>>  websearch_to_tsquery
> >>> --
> >>>  'exampl'
> >>> (1 row)
> >>>
> >>> regression=# select websearch_to_tsquery('simple', 'example');
> >>>  websearch_to_tsquery
> >>> --
> >>>  'example'
> >>> (1 row)
> >>>
> >>> If what is in your tsvector is 'exampl', then only the first of
> >>> these will match.  So IMO the question is not "why is it failing
> >>> on prod?", it's "how the heck did it work on the other machine?".
> >>> You won't get nice results if websearch_to_tsquery is using a
> >>> different TS configuration than to_tsvector did.
> >>
> >> I think this shows why we are getting the results we see.  Credit to ch
> >> on IRC for asking this question.
> >>
> >> The problem host:
> >>
> >> freshports.org=> select websearch_to_tsquery('example');
> >>  websearch_to_tsquery
> >> --
> >>  'example'
> >> (1 row)
> >
> > Ahh, this explains the differences and as to why it works where it 
> > shouldn't?
> >
> > freshports.org=> select setting, source from pg_settings where name =
> > 'default_text_search_config';
> >   setting  | source
> > ---+-
> >  pg_catalog.simple | default
> > (1 row)
> >
> >
> >>
> >> The hosts on which this search works
> >>
> >> freshports.devgit=# select websearch_to_tsquery('example');
> >>  websearch_to_tsquery
> >> --
> >>  'exampl'
> >> (1 row)
> >
> >
> > freshports.devgit=# select setting, source from pg_settings where name
> > = 'default_text_search_config';
> >   setting   |   source
> > +
> >  pg_catalog.english | configuration file
> > (1 row)
> >
> >
> > At least now I know what I can play with to get all hosts in sync.
>
> Here we go, on the problem database, create a new field, based on simple, not 
> english.
>
> ALTER TABLE public.ports
> ADD COLUMN pkgmessage_textsearchable3 tsvector generated always as 
> (to_tsvector('simple'::regconfig, translate(pkgmessage, '/'::text, ' 
> '::text))) stored;
>
> Index it:
>
> CREATE INDEX ports_pkgmessage_textsearchable3_idx
> ON public.ports USING gin
> (pkgmessage_textsearchable3)
> TABLESPACE pg_default;
> CREATE INDEX
>
> query it:
>
> freshports.org=> SELECT id as port_id, element_pathname(element_id)
>   FROM ports
>  WHERE pkgmessage_textsearchable3  @@ websearch_to_tsquery('example');
>  port_id |   element_pathname
> -+--
>34126 | /ports/head/security/pond
>74559 | /ports/branches/2015Q3/emulators/linux_base-c6
>60310 | /ports/branches/2020Q4/www/gitlab-ce
>38345 | /ports/head/www/git

Re: tsvector not giving expected results on one host

2022-12-21 Thread Oleg Bartunov
I

On Wed, Dec 21, 2022 at 1:12 PM Oleg Bartunov  wrote:
>
> Dan,
>
> it's always good to specify configuration name in a query to avoid
> recheck,  since
> websearch_to_tsquery(regconfig, text) is immutable, while
> websearch_to_tsquery(text) is stable.

immutable function calculates once in planning time, but stable
function calculates during running time,
so the difference may be very big depending on how many tuples found.

>
> See the difference:
>
> [local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
> from apod where  websearch_to_tsquery('simple','galaxies') @@ fts;
>   QUERY PLAN
> ---
>  Bitmap Heap Scan on apod (actual time=0.008..0.008 rows=0 loops=1)
>Recheck Cond: ('''galaxies'''::tsquery @@ fts)
>->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.007..0.007
> rows=0 loops=1)
>  Index Cond: (fts @@ '''galaxies'''::tsquery)
>  Planning Time: 0.134 ms
>  Execution Time: 0.022 ms
> (6 rows)
>
> Time: 0.369 ms
> [local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
> from apod where  websearch_to_tsquery('galaxies') @@ fts;
>QUERY PLAN
> -
>  Bitmap Heap Scan on apod (actual time=0.107..1.463 rows=493 loops=1)
>Filter: (websearch_to_tsquery('galaxies'::text) @@ fts)
>Heap Blocks: exact=276
>->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.059..0.059
> rows=493 loops=1)
>  Index Cond: (fts @@ websearch_to_tsquery('galaxies'::text))
>  Planning Time: 0.125 ms
>  Execution Time: 1.518 ms
> (7 rows)
>
> On Sat, Dec 17, 2022 at 11:34 PM Dan Langille  wrote:
> >
> > On Sat, Dec 17, 2022, at 3:22 PM, Dan Langille wrote:
> > > On Sat, Dec 17, 2022, at 3:14 PM, Dan Langille wrote:
> > >> On Sat, Dec 17, 2022, at 2:55 PM, Tom Lane wrote:
> > >>> "Dan Langille"  writes:
> > >>>>  pkgmessage_textsearchable2 | tsvector |   |   
> > >>>>| generated always as (to_tsvector('english'::regconfig, 
> > >>>> translate(pkgmessage, '/'::text, ' '::text))) stored
> > >>>
> > >>> That is not likely to play well with this:
> > >>>
> > >>>> freshports.org=> show  default_text_search_config ;
> > >>>>  default_text_search_config
> > >>>> 
> > >>>>  pg_catalog.simple
> > >>>
> > >>> because "english" and "simple" will stem words differently.
> > >>>
> > >>> regression=# select websearch_to_tsquery('english', 'example');
> > >>>  websearch_to_tsquery
> > >>> --
> > >>>  'exampl'
> > >>> (1 row)
> > >>>
> > >>> regression=# select websearch_to_tsquery('simple', 'example');
> > >>>  websearch_to_tsquery
> > >>> --
> > >>>  'example'
> > >>> (1 row)
> > >>>
> > >>> If what is in your tsvector is 'exampl', then only the first of
> > >>> these will match.  So IMO the question is not "why is it failing
> > >>> on prod?", it's "how the heck did it work on the other machine?".
> > >>> You won't get nice results if websearch_to_tsquery is using a
> > >>> different TS configuration than to_tsvector did.
> > >>
> > >> I think this shows why we are getting the results we see.  Credit to ch
> > >> on IRC for asking this question.
> > >>
> > >> The problem host:
> > >>
> > >> freshports.org=> select websearch_to_tsquery('example');
> > >>  websearch_to_tsquery
> > >> --
> > >>  'example'
> > >> (1 row)
> > >
> > > Ahh, this explains the differences and as to why it works where it 
> > > shouldn't?
> > >
> > > freshports.org=> select setting, source from pg_settings where name =
> > > 'default_text_search_config';
> > >   setting  | source
> > > 

Re: update field in jsonb

2017-11-22 Thread Oleg Bartunov
On Thu, Nov 23, 2017 at 4:45 AM, support-tiger  wrote:
> is there a way to update a single field in jsonb without replacing the
> entire json document - couldn't find an example
>
> for example
>
> create table test (id primary key, data jsonb);
>
> insert into test ({"name":"bill", "age":29});
>
>  ?? update test   set data->age = 30


update test set data = jsonb_set(data, '{age}', '30'::jsonb);

>
>
> --
> Support Dept
> Tiger Nassau, Inc.
> www.tigernassau.com
> 406-624-9310
>
>
>



Re: update field in jsonb

2017-11-24 Thread Oleg Bartunov
On Fri, Nov 24, 2017 at 3:46 AM, support-tiger  wrote:
> Oleg,
>
> hey, thanks so much - if you are in USA visiting Yellowstone Natl Park
> contact me and the beer is on me (maybe a Wyoming steak too!)

Seriously ?

btw, in PG 11 we expect subscription index
 update test set data[age]= '30';



>
>
>
> On 11/22/2017 11:27 PM, Oleg Bartunov wrote:
>>
>> On Thu, Nov 23, 2017 at 4:45 AM, support-tiger 
>> wrote:
>>>
>>> is there a way to update a single field in jsonb without replacing the
>>> entire json document - couldn't find an example
>>>
>>> for example
>>>
>>> create table test (id primary key, data jsonb);
>>>
>>> insert into test ({"name":"bill", "age":29});
>>>
>>>   ?? update test   set data->age = 30
>>
>>
>> update test set data = jsonb_set(data, '{age}', '30'::jsonb);
>>
>>>
>>> --
>>> Support Dept
>>> Tiger Nassau, Inc.
>>> www.tigernassau.com
>>> 406-624-9310
>>>
>>>
>>>
>>
>
> --
> Support Dept
> Tiger Nassau, Inc.
> www.tigernassau.com
> 406-624-9310
>
>
>