Convert Json Object To SQL Table

Question

I want to generate sql query dynamically. I found this tool

  http://querybuilder.js.org/demo.html

And i have the following JSON object:

{
  "condition": "AND",
  "rules": [
    {
      "id": "name",
      "field": "name",
      "type": "string",
      "input": "text",
      "operator": "equal",
      "value": "zura"
    },
    {
      "condition": "OR",
      "rules": [
        {
          "id": "category",
          "field": "category",
          "type": "integer",
          "input": "select",
          "operator": "equal",
          "value": "1"
        },
        {
          "id": "price",
          "field": "price",
          "type": "double",
          "input": "number",
          "operator": "equal",
          "value": "123"
        }
      ]
    },
    {
      "id": "in_stock",
      "field": "in_stock",
      "type": "integer",
      "input": "radio",
      "operator": "equal",
      "value": "1"
    },
    {
      "condition": "AND",
      "rules": [
        {
          "id": "category",
          "field": "category",
          "type": "integer",
          "input": "select",
          "operator": "equal",
          "value": "2"
        },
        {
          "id": "in_stock",
          "field": "in_stock",
          "type": "integer",
          "input": "radio",
          "operator": "equal",
          "value": "0"
        }
      ]
    }
  ]
}

Now i want to generate SQL Table in order to save this JSON data properly. Is there any way to generate Table, if yes please give me link or please help me to create same table

I don't see how the key values would translate to SQL datatypes, ie, VARCHAR, TEXT, etc (w3schools.com/sql/sql_datatypes_general.asp). You could use the CREATE TABLE statement (w3schools.com/sql/sql_create_table.asp), but based on your JSON values, I don't think you'd be creating a valid SQL table. For example, from your data: rules["type"] = string, string is not a valid SQL datatype, whereas VARCHAR(200) is. If you were to dynamically generate your table based on an object, I'd presume you'd need the right datatypes, at the very least, as key values. — twk
– twk, Commented May 3, 2017 at 20:25
Data type does not meter, first i need just Table(May Be tables) structure to save my JSON data. I can change data type in table any time. — Zurab Katsitadze
– Zurab Katsitadze, Commented May 3, 2017 at 20:29
In order to build the structure of an SQL table, you first have to CREATE TABLE (see link in my last comment). When you create the table, you have to define its structure, which requires defining a data type. IE, the SQL table has to know what type of data to expect (numbers, text, etc). As far as I know, you can't create an SQL table without setting data types for the properties you are trying to capture. It's a requirement when the table is created. A lot of people use MySQL Workbench to draft up their vision and the program spits out the SQL table for them. — twk
– twk, Commented May 3, 2017 at 20:35
I know how to create TABLE sql, but i do not know how to create TABLE structure to save my JSON. — Zurab Katsitadze
– Zurab Katsitadze, Commented May 3, 2017 at 20:38

elixenide · Accepted Answer · 2019-03-18 02:59:24Z

-1

Your Json data needs to be decoded using recursive SQL‌ function.You first need to create a self-referenced table like this:

    CREATE TABLE jsonCondition(
ConditionId INT IDENTITY,
ParentCondotionId INT ,
Id NVARCHAR(20),
Field NVARCHAR(20),
Type NVARCHAR(20),
Input NVARCHAR(20),
Operator NVARCHAR(20),
Value NVARCHAR(20) 
)

then please refer to my other json recursive Conversion to SQL at: How to generate hierarchical JSON data with Microsoft SQL Server 2016?

edited Mar 18, 2019 at 2:59

elixenide

44.9k16 gold badges79 silver badges103 bronze badges

answered May 6, 2017 at 18:49

Afshin Amiri

3,6231 gold badge22 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Convert Json Object To SQL Table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related