query_gen.sql

SELECT assert(generate_expression(
  '{"a": {"col": "abc"}}'), 
  'a = abc');
SELECT assert(generate_expression(
  '{"a": {"not": 2}}'),
  'a <> 2');
SELECT assert(generate_expression(
  '{"a": {"not": [{"gt": 2}, {"lt": 2}]}}'),
  'NOT (a > 2 AND a < 2)');
SELECT assert(generate_expression(
  '{"a": {"not": [{"gt": {"col": "abcDef"}}, {"lt": 2}]}}'),
  'NOT (a > "abcDef" AND a < 2)');
SELECT assert(generate_expression(
  '{"a": {"not": {"or": [{"gt": 2}, {"lt": 2}]}}}'),
  'NOT ((a > 2 OR a < 2))');
SELECT assert(generate_expression(
  '{"a": {"not": {"gt": 2}}}'),
  'NOT (a > 2)');
SELECT assert(generate_expression(
  '{"a": {"not": [{"col": "abc"},2,3]}}'),
  'NOT IN (abc, 2, 3)');
SELECT assert(generate_expression(
  '{"or": {"a": 1, "not": {"a": {"gt": 5, "lt": 10}}}}'),
  '(a = 1 OR NOT (a > 5 AND a < 10))');
SELECT assert(generate_expression('{"or": {"z": "null", "x": {"col": "null"}}, "and": {"a": 1, "b": 2}}')
  '(x = "null" OR z = NULL) AND (a = 1 AND b = 2)');
SELECT assert(generate_expression(
  '{"or": [{"and": [{"z": "null", "x": "null"}, {"x": "1"}]}, {"z": "null"}], "and": {"a": 1, "b": 2}}'),
  '(x = NULL AND z = NULL AND x = ''1'' OR z = NULL) AND (a = 1 AND b = 2)');
SELECT assert(generate_expression(
  '{"and": [{"or": [{"z": "null", "x": "null"}, {"x": {"col": "abc"}}]}, {"or": [{"z": "null"}, {"z": 2}]}]}'),
  '(x = NULL AND z = NULL OR x = abc) AND (z = NULL OR z = 2)');
SELECT assert(generate_expression(
  '{"not": {"or": {"z": "null", "x": "null"}}}'),
  'NOT ((x = NULL OR z = NULL))');
SELECT assert(generate_expression(
  '{"a": {"in": [1,2]}, "b": {"in": 1}}'),
  'a IN (1, 2) AND b = 1');
SELECT assert(generate_expression(
  '{"a": {"or":{"in": [1,2], "gt": 1}}}'),
  '(a > 1 OR a IN (1, 2))');
SELECT assert(generate_expression(
  '{"a": {"or":[{"in": [1,2]}, {"in": 1}]}}'),
  '(a IN (1, 2) OR a = 1)');
SELECT assert(generate_expression(
  '{"a": {"and":[{"in": [1,2]}, {"or": {"in": 1, "lt": 2}}]}}'),
  'a IN (1, 2) AND (a = 1 OR a < 2)');
SELECT assert(generate_expression(
  '{"a": {"between": [{"col": "abc"},2]}, "b": {"between": 1}}'),
  'a BETWEEN abc AND 2');
SELECT assert(generate_expression(
  '{"a": 1, "and": [{"or": {"z": "null", "x": "null"}}, {"f": null}]}'),
  'a = 1 AND (x = NULL OR z = NULL) AND f = NULL');
SELECT assert(generate_expression($$
{
  "or": [
    {
      "a": {
        "gt": 1,
        "lte": 2
      }
    },
    {
      "b": "test2"
    }
  ],
  "a": 1,
  "and": [
    {
      "or": {
        "z": "null",
        "x": "null"
      }
    },
    {
      "f": {"between": [{"col": "abc"}, 888]}
    }
  ],
  "not": {
    "test_in": [
      1,
      "2 ''z",
      3,
      {"a": 1},
      "null"
    ]
  }
}
$$), 
$$a = 1 AND (a > 1 AND a <= 2 OR b = 'test2') AND (x = NULL OR z = NULL) AND f BETWEEN abc AND 888 AND NOT (test_in IN (1, '2 ''''z', 3, NULL))$$);