Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--https://stackoverflow.com/q/78146108/5298879
CREATE TABLE test_data_table (   id    BIGSERIAL PRIMARY KEY,
                                 state  text,
                                 city text);

INSERT INTO test_data_table (state, city)
    values  ('MD', 'Union Bridge'),
            ('IL', 'Bourbonnais'),
            ('NC', 'Rdelk'),
            ('FL', 'Bonita Springs'),
            ('FL', 'Unit Ave Maria'),
            ('AZ', 'N Placita Chula Vista Tucson'),
            ('TX', 'Sienna'),
            ('LA', 'Lornager'),
            ('CA', 'Empire'),
            ('MA', 'Middleton'),
            ('CO', 'Yampa'),
            ('GA', 'Spr Project Name Unit'),
            ('IN', 'Greencastle'),
            ('NV', 'Flora'),
            ('RI', 'Coventry'),
            ('WA', 'Rice'),
            ('FL', 'Orange City'),
            ('TX', 'Haltom City'),
            ('CA', 'Moraga'),
            ('LA', 'Belle Chasse'),
            ('AZ', 'E Rincon Creek Ranch Rd Tucson'),
            ('CA', 'Acampo'),
            ('AZ', 'S Avra Rd Tucson'),
            ('GA', 'Folkston'),
            ('FL', 'Biscayne Park'),
            ('FL', 'All Units Oxford'),
            ('IL', 'Volo'),
            ('MN', 'Peterson'),
            ('LA', 'Rayville'),
            ('KY', 'South Park View'),
            ('AZ', 'E Camino Emmanuel Sahuarita'),
            ('CA', 'Wrighwood'),
            ('CA', 'Los Angeles'),
            ('AZ', 'N Teal Blue Tr Tucson'),
            ('NY', 'Clifton Park'),
            ('IN', 'Frankl'),
            ('KY', 'Anchorage'),
            ('LA', 'Crown Point'),
            ('CA', 'Los BanosStruct CalcsSpan T'),
            ('IL', 'Chebanse') returning *;

CREATE TABLE test_filter_values (
    id    BIGSERIAL PRIMARY KEY,
    type  text,
    value jsonb );

INSERT INTO test_filter_values (type, value) VALUES 
 ('CITY', (SELECT json_agg(DISTINCT city)::jsonb FROM test_data_table))
,('STATE', (SELECT json_agg(DISTINCT state)::jsonb FROM test_data_table))
returning *;

CREATE TABLE




id
state
city


1
MD
Union Bridge

2
IL
Bourbonnais

3
NC
Rdelk

4
FL
Bonita Springs

5
FL
Unit Ave Maria

6
AZ
N Placita Chula Vista Tucson

7
TX
Sienna

8
LA
Lornager

9
CA
Empire

10
MA
Middleton

11
CO
Yampa

12
GA
Spr Project Name Unit

13
IN
Greencastle

14
NV
Flora

15
RI
Coventry

16
WA
Rice

17
FL
Orange City

18
TX
Haltom City

19
CA
Moraga

20
LA
Belle Chasse

21
AZ
E Rincon Creek Ranch Rd Tucson

22
CA
Acampo

23
AZ
S Avra Rd Tucson

24
GA
Folkston

25
FL
Biscayne Park

26
FL
All Units Oxford

27
IL
Volo

28
MN
Peterson

29
LA
Rayville

30
KY
South Park View

31
AZ
E Camino Emmanuel Sahuarita

32
CA
Wrighwood

33
CA
Los Angeles

34
AZ
N Teal Blue Tr Tucson

35
NY
Clifton Park

36
IN
Frankl

37
KY
Anchorage

38
LA
Crown Point

39
CA
Los BanosStruct CalcsSpan T

40
IL
Chebanse



INSERT 0 40



CREATE TABLE




id
type
value


1
CITY
["Acampo", "All Units Oxford", "Anchorage", "Belle Chasse", "Biscayne Park", "Bonita Springs", "Bourbonnais", "Chebanse", "Clifton Park", "Coventry", "Crown Point", "E Camino Emmanuel Sahuarita", "E Rincon Creek Ranch Rd Tucson", "Empire", "Flora", "Folkston", "Frankl", "Greencastle", "Haltom City", "Lornager", "Los Angeles", "Los BanosStruct CalcsSpan T", "Middleton", "Moraga", "N Placita Chula Vista Tucson", "N Teal Blue Tr Tucson", "Orange City", "Peterson", "Rayville", "Rdelk", "Rice", "S Avra Rd Tucson", "Sienna", "South Park View", "Spr Project Name Unit", "Union Bridge", "Unit Ave Maria", "Volo", "Wrighwood", "Yampa"]

2
STATE
["AZ", "CA", "CO", "FL", "GA", "IL", "IN", "KY", "LA", "MA", "MD", "MN", "NC", "NV", "NY", "RI", "TX", "WA"]



INSERT 0 2


SELECT DISTINCT city from test_data_table
WHERE city ILIKE 'b%';
--This query returns 4 results; 


city


Bourbonnais

Biscayne Park

Belle Chasse

Bonita Springs



SELECT 4


--And this one:
SELECT city FROM (SELECT jsonb_array_elements(value) AS city
                  FROM test_filter_values WHERE type = 'CITY') cities
WHERE city::text ILIKE 'b%';
--Returns no results, LIKE operator without % at the begining 
--or in the end returs zero results from test_filter_values table, 
--but if using LIKE '%b%' with such case the behaviour is the same. 


city



SELECT 0


--The same issue happens with the exact match:

SELECT state FROM (SELECT jsonb_array_elements(value) AS state
                  FROM test_filter_values WHERE type = 'STATE') states
WHERE state::text = 'NC';


state



SELECT 0


SELECT state
FROM test_filter_values
CROSS JOIN jsonb_array_elements_text(value)_(state)
WHERE type = 'STATE'
AND state = 'NC';


state


NC



SELECT 1


SELECT jsonb_path_query( value
                        ,'$[*]?(@ like_regex "^b.*" flag "i")'
                       )#>>'{}' AS city
FROM test_filter_values 
WHERE type = 'CITY';


city


Belle Chasse

Biscayne Park

Bonita Springs

Bourbonnais



SELECT 4

id	state	city
1	MD	Union Bridge
2	IL	Bourbonnais
3	NC	Rdelk
4	FL	Bonita Springs
5	FL	Unit Ave Maria
6	AZ	N Placita Chula Vista Tucson
7	TX	Sienna
8	LA	Lornager
9	CA	Empire
10	MA	Middleton
11	CO	Yampa
12	GA	Spr Project Name Unit
13	IN	Greencastle
14	NV	Flora
15	RI	Coventry
16	WA	Rice
17	FL	Orange City
18	TX	Haltom City
19	CA	Moraga
20	LA	Belle Chasse
21	AZ	E Rincon Creek Ranch Rd Tucson
22	CA	Acampo
23	AZ	S Avra Rd Tucson
24	GA	Folkston
25	FL	Biscayne Park
26	FL	All Units Oxford
27	IL	Volo
28	MN	Peterson
29	LA	Rayville
30	KY	South Park View
31	AZ	E Camino Emmanuel Sahuarita
32	CA	Wrighwood
33	CA	Los Angeles
34	AZ	N Teal Blue Tr Tucson
35	NY	Clifton Park
36	IN	Frankl
37	KY	Anchorage
38	LA	Crown Point
39	CA	Los BanosStruct CalcsSpan T
40	IL	Chebanse

id	type	value
1	CITY	["Acampo", "All Units Oxford", "Anchorage", "Belle Chasse", "Biscayne Park", "Bonita Springs", "Bourbonnais", "Chebanse", "Clifton Park", "Coventry", "Crown Point", "E Camino Emmanuel Sahuarita", "E Rincon Creek Ranch Rd Tucson", "Empire", "Flora", "Folkston", "Frankl", "Greencastle", "Haltom City", "Lornager", "Los Angeles", "Los BanosStruct CalcsSpan T", "Middleton", "Moraga", "N Placita Chula Vista Tucson", "N Teal Blue Tr Tucson", "Orange City", "Peterson", "Rayville", "Rdelk", "Rice", "S Avra Rd Tucson", "Sienna", "South Park View", "Spr Project Name Unit", "Union Bridge", "Unit Ave Maria", "Volo", "Wrighwood", "Yampa"]
2	STATE	["AZ", "CA", "CO", "FL", "GA", "IL", "IN", "KY", "LA", "MA", "MD", "MN", "NC", "NV", "NY", "RI", "TX", "WA"]