Insert a JSON input into a MySQL table

Question

I need to insert the below JSON into a MySQL table as below.

{
  "RelationshipType" : [ 
    {
        "ID" : 1,
        "FromID" : 70,
        "ToID" : 12
    },
    {
        "ID" : 3,
        "FromID" : 80,
        "ToID" : 1
    }
  ]
}

Expected output

ID    |    FromID    |    ToID    |
1     |     70       |     12     |
3     |     80       |     1      |

I used the below code to do the same in SQL Server. Is there any matching function to OPENJSON in MySQL.

DECLARE @Relationship AS TABLE(FromID INT,ToID INT,ID INT)
INSERT INTO @Relationship (FromID,ToID,ID)
SELECT FromID, ToID, ID
FROM OPENJSON(@RelationshipType)
WITH (FromID INT, ToID INT, ID INT)

Look at inserting the JSON document into a JSON data type column and then using JSON_TABLE to temporarily change the unstructured JSON data into a structured table. — Dave Stokes
– Dave Stokes, Commented Aug 2, 2019 at 11:38
Thanks, @DaveStokes, Could you please provide any reference link or answer if you have — Harsha W
– Harsha W, Commented Aug 3, 2019 at 18:10
For an intro to JSON_TABLE() see elephantdolphin.blogspot.com/2019/05/… — Dave Stokes
– Dave Stokes, Commented Aug 5, 2019 at 13:48

Bill Karwin · Accepted Answer · 2019-08-04 15:10:53Z

1

Below is an example using JSON_TABLE(). I tested this on MySQL 8.0.16 in my sandbox environment.

mysql> create table relationship(id int primary key, fromid int, toid int);

mysql> insert into relationship select * from json_table(
'{ "RelationshipType" : [ { "ID" : 1, "FromID" : 70, "ToID" : 12 }, { "ID" : 3, "FromID" : 80, "ToID" : 1 } ] }',
'$.RelationshipType[*]' columns (
  id int path '$.ID',
  fromid int path '$.FromID',
  toid int path '$.ToID'
)) as j
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from relationship;
+----+--------+------+
| id | fromid | toid |
+----+--------+------+
|  1 |     70 |   12 |
|  3 |     80 |    1 |
+----+--------+------+

The JSON_TABLE() function requires MySQL 8.0.4 or later. It isn't implemented in earlier versions of MySQL.

answered Aug 4, 2019 at 15:10

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

Harsha W Over a year ago

Do you have any solution for this stackoverflow.com/questions/57475029/…

Bill Karwin Over a year ago

That's a duplicate question, another user gave you a link, and I voted to close your question as a duplicate.

Collectives™ on Stack Overflow

Insert a JSON input into a MySQL table

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