MariaDB data type automatically change json to longtext even if i choose data type to json

Question

This is exactly what happened when i select data type to json

Does this only happen with phpMyAdmin, or also when you do it in the mysql CLI? — Barmar, Jun 09 '21 at 21:56
I can't reproduce the problem: https://www.db-fiddle.com/f/pnKWiNgJBTDR9fNwykLNAA/0 — Barmar, Jun 09 '21 at 22:10
I choose json when i am craeting the table but it replace automatically by longtext — Raihan Chowdhury, Jun 09 '21 at 22:15
Is your database MySQL or MariaDB? MariaDB doesn't have JSON, and converts it automatically to LONGTEXT. — Barmar, Jun 09 '21 at 22:21
Thank u, yes my database is mariaDB but how can I convert it to mysql ? Bcz need store JSON in database — Raihan Chowdhury, Jun 09 '21 at 22:23
They're totally different products, you need to install MySQL from scratch. — Barmar, Jun 09 '21 at 22:29
You can still store JSON in the database. It just doesn't use a special representation, it stores it as text. — Barmar, Jun 09 '21 at 22:30
Thank u. I know I can use json stringify and json parse to store and read. But i dont want to do it. I want to store as Json and retrieve as json — Raihan Chowdhury, Jun 09 '21 at 22:37
[Read this](https://mariadb.com/kb/en/json-data-type/). And there's a difference between MariaDB's [plain LONGTEXT vs Json LONGTEXT data type](https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=e2feaf39e86e2888d441a53bcc5add2b) — FanoFN, Jun 10 '21 at 00:20
Thank u. That means in mariaDb we can not store json as object but can store as longText . — Raihan Chowdhury, Jun 10 '21 at 10:54
JSON is text. SQL statements are also text. You need to make objects into JSON (text) to store them, regardless of how the db stores them. — Garr Godfrey, Mar 27 '22 at 04:08

score 3 · Answer 1 · answered Mar 27 '22 at 04:02

3

From MariaDB website:

JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent.

https://mariadb.com/kb/en/json-data-type/

answered Mar 27 '22 at 04:02

Beamer

758
5
11

This explains the JSON type from the manual, without any context, but doesn't offer any real solution to the problem. please consider editing your comment. – Nioooooo Feb 28 '23 at 14:00
There is no solution because there is no problem. Read the question and my answer few more times, think about it, then please confirm your understanding and consider upvoting my answer back. – Beamer Mar 01 '23 at 16:30

score -3 · Accepted Answer · answered Jun 10 '21 at 13:35

Solution: MariaDB doesn't have JSON, and converts it automatically to LONGTEXT

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=e2feaf39e86e2888d441a53bcc5add2b

So, I change mariaDb to MySQL in xamp. Now i can set Json data type

how to change: https://stackoverflow.com/a/58973750/16180226

Note: Actually I am sending request using node js APi.

here is req body: (ignore field this for testing purpouse)

Here data is the req body

Query: 'INSERT INTO table SET ?', data

    {
        "name": "Test",
        "description": [
            {
                "hello": "description"
            },
            {
                "hello": "description"
            },
            {
                "hello": "description"
            }
        ],
        "monthlySell": {
            "hello": "monthlySell"
        }
    }

}

Code to Stringify if req body key has object field:

Now i dont have to worry about stringify, it will automatically stringyfiy to store Json if a filed is object

    const keys = Object.keys(data);
    console.log(keys.length)
    for (let i=0; i<keys.length; i++)
    {
        if(typeof data[keys[i]]==='object')
        {
            console.log(typeof keys[i] +data[keys[i]] )
            data[keys[i]]=JSON.stringify(data[keys[i]])
        }
    
    }

MariaDB does have JSON but LONGTEXT is used as an alias because of differences in standards. — Beamer, Mar 27 '22 at 04:09
`node-mysql` is doing a stringify for you if it detects the column type is JSON. — Garr Godfrey, Mar 27 '22 at 04:11

MariaDB data type automatically change json to longtext even if i choose data type to json

2 Answers2