Filtering jsonb properties with `where` clause-CodePudding

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.

CodePudding user response：

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.

As such, if you want all conditions to be met, you have to use logical and:

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

CodePudding user response：

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.