1

I'm trying to use python library sqlparse to extract the WHERE condition from a SQL statement and represent it in conditional JSON format. I'm interested only in the SQL condition, so the starting point is a dummy SQL statement, i.e. select * from Table where Col1= 'aaa' AND Col2 > 10 which should be converted in a nested-JSON format like this:

{"and" : [
   {"eq": {"Col1","aaa"} },
   {"gt" : {"Col2",10}}
]}

The idea was to use the AST of the WHERE clause to generate this JSON structure, but I didn't manage to do it. I'm also open to suggestion regarding different python libraries.

The solution should be able to handle different kinds of operators, like =, <>, >, <, regex, IN, ... but also a first basic solution would be very helpful. Also the complexity of the WHERE condition shouldn't be an issue. We should find some kind of recursive method which reflects the structure in the JSON format.

Nicola
  • 11
  • 2

1 Answers1

1

You can use my library SQLGlot to get the AST, and then you can convert it into JSON.

import sqlglot
import sqlglot.expressions as exp

sql = """
select * from x where  Col1= 'aaa' AND Col2 > 10
"""

where = sqlglot.parse_one(sql).find(exp.Where)

print(where)

(WHERE this:
  (AND this:
    (EQ this:
      (COLUMN this:
        (IDENTIFIER this: Col1, quoted: False)), expression:
      (LITERAL this: aaa, is_string: True)), expression:
    (GT this:
      (COLUMN this:
        (IDENTIFIER this: Col2, quoted: False)), expression:
      (LITERAL this: 10, is_string: False))))
Toby Mao
  • 374
  • 2
  • 6