Support for Static SQL Generation with Variable JSONB Query Conditions in SQLC

[meifannao]

What do you want to change?

I am currently using SQLC, a static SQL framework, for my PostgreSQL database interactions. One of the challenges I face is querying JSONB data types where the query conditions can vary significantly and are unknown at compile time.

Database Schema:

CREATE TABLE tb_spu (
  id SERIAL PRIMARY KEY,
  name varchar(32) NOT NULL DEFAULT '',
  param_list JSONB NOT NULL DEFAULT '{}'
);

Example JSONB Data:

{
  "data": [
    {
      "id": 6828,
      "kv": [
        {"id": 16476, "value": "2011-03-06"},
        {"id": 18703, "value": "Quidem autem blanditiis qui dolor veniam."},
        {"id": 16964, "value": "Lady Berenice Jerde"}
      ]
    },
    {
      "id": 14969,
      "kv": [
        {"id": 6006, "value": "2008-06-20"},
        {"id": 18859, "value": null},
        {"id": 11194, "value": "Mrs. Verlie Blick"},
        {"id": 5097, "value": "1990-12-06"},
        {"id": 17509, "value": "Lady Lorena Tillman"},
        {"id": 10348, "value": "Miss Ona Thompson"},
        {"id": 9818, "value": "Mrs. Adriana Paucek"}
      ]
    }
  ]
}

Current Approach:
For single condition queries, we use:

SELECT *
FROM tb_spu
WHERE jsonb_path_exists(param_list, '$.data[*].kv[*] ? (@.id == 16476 && @.value == "2011-03-06")');

Issue:
need to perform queries based on multiple conditions dynamically inserted into the query, such as:

Single condition: [{"id": 16476, "value": "2011-03-06"}]
Multiple conditions: [{"id": 16476, "value": "2011-03-06"}, {"id": 9818, "value": "Mrs. Adriana Paucek"}]
Varied conditions: [{"id": 9818, "value": "Mrs. Adriana Paucek"}, {"id": 16476, "value": "2011-03-06"}, {"id": 6006, "value": "2008-06-20"}]
Because SQLC is a static SQL framework, I am unable to dynamically construct these queries at runtime. I am looking for a solution that would allow me to generate SQL queries statically but can accommodate varying conditions without manual query construction for each possible scenario.

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go