Read and write from MySQL with JSON datatype

[midhunrajs]

Hi,

I have been trying to read and insert JSON data from MySQL database. Is there any built in APIs for doing this? DBTools APIs giving me datatype error while reading.

Given below the table structure

Table Name: device_details

Columns:Sl_No(INT),Details(JSON)

[aputman]

Do you need to use the JSON datatype or would it work better for you as a string?  It appears that the JSON datatype is a binary format so that it can go thru server-side optimizations for storage and data verification.  If these benefits aren't required, I would suggest storing your data as a string instead.  You can review the advantages of JSON datatype on MySQL website.  If you choose to stay with the JSON datatype, I'm not entirely sure how you go about exporting the JSON data over the binary data.  The linked website may give you some ideas. You will probably need to use the DBtools Execute Query VI to pull the data, using the query design in the link.

SELECT JSON_TYPE('["a", "b", 1]');

[drjdpowell]

I've used the MySQL JSON datatype, though with a TCP connection, rather than DBTools.  It's stored in binary, but is inserted and selected in text.