Filtering jsonb properties with `where` clause

Question

Can't get working where statement in sql query.

Table structure:

create table some_table
(
    value jsonb not null
);

Some test data:

INSERT INTO some_table (value) VALUES ('{"id": "480e68b1-f23b-4f5a-9dff-2bd91b4153a5", "appointmentDate": "2021-12-20", "appointmentTime": "10:10:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "1ee12ca6-bd44-4da8-a29f-c7f989abcdda", "appointmentDate": "2021-12-21", "appointmentTime": "10:10:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "adfd20dc-7ce7-4d1d-9dfc-013f889a69a8", "appointmentDate": "2021-12-22", "appointmentTime": "10:10:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "1ea7a1d3-2266-466b-8907-d96afa036759", "appointmentDate": "2021-12-23", "appointmentTime": "10:10:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "ddfd20dc-7ce7-4d1d-9dfc-013f889a69a9", "appointmentDate": "2021-12-24", "appointmentTime": "10:10:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "bdfd20dc-7ce7-4d1d-9dfc-013f889a69a9", "appointmentDate": "2021-12-25", "appointmentTime": "10:10:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "2b437993-6100-450e-85e6-21e87e007010", "appointmentDate": "2021-12-29", "appointmentTime": "10:10:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "947115f2-a192-40fb-8c21-078e91952236", "appointmentDate": "2021-12-31", "appointmentTime": "10:11:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "bcf4b057-e954-4159-8653-c33a72941b20", "appointmentDate": "2022-01-20", "appointmentTime": "09:11:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "717b7c2b-a514-4227-8280-ff3a4fa01c21", "appointmentDate": "2022-01-20", "appointmentTime": "08:10:00"}');
INSERT INTO some_table (value) VALUES ('{"id": "078fe91b-8921-47fb-a2d1-61ec3f11cbc6", "appointmentDate": "2022-01-20", "appointmentTime": "10:10:00"}');

Query:

select some_table.value
from some_table
where ((value ->> 'appointmentDate'), (value ->> 'appointmentTime'), (value ->> 'id')) > ('2021-12-20', '10:10', '480e68b1-f23b-4f5a-9dff-2bd91b4153a5')
order by (value ->> 'appointmentDate') asc, (value ->> 'appointmentTime') asc, (value ->> 'id') asc fetch next 100 rows only

Output (all data):

"{""id"": ""480e68b1-f23b-4f5a-9dff-2bd91b4153a5"", ""appointmentDate"": ""2021-12-20"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""1ee12ca6-bd44-4da8-a29f-c7f989abcdda"", ""appointmentDate"": ""2021-12-21"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""adfd20dc-7ce7-4d1d-9dfc-013f889a69a8"", ""appointmentDate"": ""2021-12-22"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""1ea7a1d3-2266-466b-8907-d96afa036759"", ""appointmentDate"": ""2021-12-23"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""ddfd20dc-7ce7-4d1d-9dfc-013f889a69a9"", ""appointmentDate"": ""2021-12-24"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""bdfd20dc-7ce7-4d1d-9dfc-013f889a69a9"", ""appointmentDate"": ""2021-12-25"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""2b437993-6100-450e-85e6-21e87e007010"", ""appointmentDate"": ""2021-12-29"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""947115f2-a192-40fb-8c21-078e91952236"", ""appointmentDate"": ""2021-12-31"", ""appointmentTime"": ""10:11:00""}"
"{""id"": ""717b7c2b-a514-4227-8280-ff3a4fa01c21"", ""appointmentDate"": ""2022-01-20"", ""appointmentTime"": ""08:10:00""}"
"{""id"": ""bcf4b057-e954-4159-8653-c33a72941b20"", ""appointmentDate"": ""2022-01-20"", ""appointmentTime"": ""09:11:00""}"
"{""id"": ""078fe91b-8921-47fb-a2d1-61ec3f11cbc6"", ""appointmentDate"": ""2022-01-20"", ""appointmentTime"": ""10:10:00""}"

Why filtering by id in query doesn't work and all data is returned?

When do query only by id:

select some_table.value
from some_table
where (value ->> 'id') > '480e68b1-f23b-4f5a-9dff-2bd91b4153a5'
order by (value ->> 'appointmentDate') asc, (value ->> 'appointmentTime') asc, (value ->> 'id') asc fetch next 100 rows only

Output:

"{""id"": ""adfd20dc-7ce7-4d1d-9dfc-013f889a69a8"", ""appointmentDate"": ""2021-12-22"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""ddfd20dc-7ce7-4d1d-9dfc-013f889a69a9"", ""appointmentDate"": ""2021-12-24"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""bdfd20dc-7ce7-4d1d-9dfc-013f889a69a9"", ""appointmentDate"": ""2021-12-25"", ""appointmentTime"": ""10:10:00""}"
"{""id"": ""947115f2-a192-40fb-8c21-078e91952236"", ""appointmentDate"": ""2021-12-31"", ""appointmentTime"": ""10:11:00""}"
"{""id"": ""717b7c2b-a514-4227-8280-ff3a4fa01c21"", ""appointmentDate"": ""2022-01-20"", ""appointmentTime"": ""08:10:00""}"
"{""id"": ""bcf4b057-e954-4159-8653-c33a72941b20"", ""appointmentDate"": ""2022-01-20"", ""appointmentTime"": ""09:11:00""}"

So filtering works.

I need such query for keyset pagination. That sql statement is generated by jooq.

score 0 · Answer 1 · answered Feb 21 '22 at 01:40

There's a typo in your test query. 10:10 should be 10:10:00 to match your test data.

The comparison being performed for the first row can be checked in isolation:

select (('2021-12-20'), ('10:10:00'), ('480e68b1-f23b-4f5a-9dff-2bd91b4153a5')) > ('2021-12-20', '10:10', '480e68b1-f23b-4f5a-9dff-2bd91b4153a5')

... which is true because the string 10:10:00 is > 10:10, so the test row is included.

score 0 · Answer 2 · answered Feb 21 '22 at 03:22

0

I'm not exactly 100% sure how a predicate like (a, b, c) > (v1, v2, v3) is evaluated, but it seems only the first condition is actually being checked.

Well, yes. That is how it works. The 2nd condition only matters when the first one is tied. The 3rd only matters if the first two are tied. This is exactly what you need for key-set pagination.

answered Feb 21 '22 at 03:22

jjanes

37,812
5
27
34

Could you please describe what does it mean "tied condition"? – Woland Feb 21 '22 at 07:57
tied means equal to each other. – jjanes Feb 21 '22 at 15:37

Filtering jsonb properties with `where` clause

Why filtering by id in query doesn't work and all data is returned?

2 Answers2