phpmyadmin Question posted in
The official documentation can be found here.

Having problems entering JSON into MySQL database – Phpmyadmin

I am trying to develop a system to handle data from a third party site. I will enter their data in my MySQL DB. Part of their data is a JSON string. I am new to JSON in MySQL. I have read and watched a tutorial on how to query JSON. I have done so successfully with JSON strings that I have entered. The problem is I cannot get their string to insert into my table. I get an error that says,”#3140 – Invalid JSON text: “The document root must not follow by other values.” at position 11 in value for column ‘ordered_shed.items’. Below is the JSON I received from the company. I have looked at it and cannot figure out what the problem is. I am using PHPMyAdmin to insert the code. The structure is.

id int(11) AUTO_INCREMENT
items JSON

"lineItems":[
{ "description": "details-item-size",
"productKey": "size",
"quantity": 1
},
{ "description": "details-item-style",
"model": "",
"price": 2229,
"productKey": "style",
"quantity": 1,
"value": ""
},
{ "description": "details-item-flooring",
"price": 0,
"productCategory": "flooring-interior",
"quantity": 1
},
{ "description": "details-item-floor-joist",
"productCategory": "flooring-interior",
"quantity": 1
},
{ "description": "details-item-roof-overhang",
"productKey": "RoofOverhang",
"quantity": 1
},
{ "description": "details-item-sidewall-height",
"productKey": "wall-height",
"quantity": 1
},
{ "description": "standard",
"productCategory": "structure",
"quantity": 1
},
{ "description": "Pressure Treated Skids",
"productCategory": "structure",
"quantity": 1
},
{ "description": "details-item-loft",
"productCategory": "flooring-interior",
"quantity": 1
},
{ "description": "details-item-roof-material",
"productKey": "roof-material",
"quantity": 1
},
{ "description": "details-item-siding-color",
"quantity": 1,
"productKey": "siding-color"
},
{ "description": "details-item-siding",
"productKey": "siding",
"quantity": 1
},
{ "description": "details-item-roof-color",
"quantity": 1,
"productKey": "roof-color"
},
{ "description": "details-item-trim-color",
"quantity": 1,
"productKey": "trim-color"
}]

Any help would be greatly appreciated.

Tags:

2

Answers


  1. 0

    The JSON you’ve provided is not valid. You can always check here.

    I think you just need your JSON to look like this:

    {
    "lineItems": [{
            "description": "details-item-size",
            "productKey": "size",
            "quantity": 1
        },
        {
            "description": "details-item-style",
            "model": "",
            "price": 2229,
            "productKey": "style",
            "quantity": 1,
            "value": ""
        },
        {
            "description": "details-item-flooring",
            "price": 0,
            "productCategory": "flooring-interior",
            "quantity": 1
        },
        {
            "description": "details-item-floor-joist",
            "productCategory": "flooring-interior",
            "quantity": 1
        },
        {
            "description": "details-item-roof-overhang",
            "productKey": "RoofOverhang",
            "quantity": 1
        },
        {
            "description": "details-item-sidewall-height",
            "productKey": "wall-height",
            "quantity": 1
        },
        {
            "description": "standard",
            "productCategory": "structure",
            "quantity": 1
        },
        {
            "description": "Pressure Treated Skids",
            "productCategory": "structure",
            "quantity": 1
        },
        {
            "description": "details-item-loft",
            "productCategory": "flooring-interior",
            "quantity": 1
        },
        {
            "description": "details-item-roof-material",
            "productKey": "roof-material",
            "quantity": 1
        },
        {
            "description": "details-item-siding-color",
            "quantity": 1,
            "productKey": "siding-color"
        },
        {
            "description": "details-item-siding",
            "productKey": "siding",
            "quantity": 1
        },
        {
            "description": "details-item-roof-color",
            "quantity": 1,
            "productKey": "roof-color"
        },
        {
            "description": "details-item-trim-color",
            "quantity": 1,
            "productKey": "trim-color"
        }
    ]
}
    Login or Signup to reply.
  2. 0

    You can use

    SELECT json_valid(<jscol>) as "isValid?"

    to check whether json column is valid. If returns 1 then it’s valid. Invalid if it returns 0 (zero), and you can insert like this :

    insert into tab(items) 
select case when json_valid(<jscol>)=1 then
                            <jscol>
       end;

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search