Transform JSON query filter into SQL?

Question

I'm looking for a java library that can translate json input into SQL dynamically.

For example, the library could expect the following json data:

{
    "rules": [
        {
            "field": "firstname",
            "value": [
                "John",
                "Doe"
            ],
            "operator": "in"
        },
        {
            "rules": [
                {
                    "field": "age",
                    "value": 18,
                    "operator": "EQUALS"
                }
            ],
            "condition": "AND"
        }
    ]
}

And should then be able of translating this into a dynamic sql query:

SELECT * FROM persons where firstname IN ("John", "Doe") AND age = 18;

Is there any existing framework that I could build this upon?

It’s not exactly what you want, but maybe somehow you’d be able to bend your requirements? Anyway, I created this library: github.com/mmalek06/JsonSql . Maybe instead of creating filters like you do, you could directly create sql queries? — Marek M.
– Marek M., Commented Feb 29, 2020 at 22:04

Naveen Kumar · Accepted Answer · 2020-02-27 10:42:56Z

1

I am not sure about library to get the expected output. But you can use the following code to write Java method.

Assuming that input will be JSON object.

    JSONObject jo = (JSONObject) obj; 

    // getting fieldname and operator
    String field= (String) jo.get("field"); 
    String operator= (String) jo.get("operator"); 
    //getting values
    JSONArray ja = (JSONArray) jo.get("value"); 

    // iterating values
    Iterator itr2 = ja.iterator();

Then you can use above to write dynamic SQL query.

answered Feb 27, 2020 at 10:42

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

membersound Over a year ago

Yes but I'd have to implement the specific sql syntax for parsing. I hoped there could be a library so I'd not have to reinvent the wheel.

Naveen Kumar Over a year ago

You can check this url: quora.com/What-is-the-best-way-to-convert-JSON-to-an-SQL-Query

Collectives™ on Stack Overflow

Transform JSON query filter into SQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related