CODEWITHSUNDEEP
mariadb
user1669296
user1669296

Reputation: 445

What is the syntax to extract JSON Value from column in Maria DB database?

I am using MariaDB 10.2.8. What is the syntax necessary to extract the value from the key "2" in the below JSON using the JSON_EXTRACT() function

{
  "1":"Windows 10",
  "2":"Windows 8",
  "3":"Windows 7",
  "4":"MAC OS"
 }

Upvotes: 6

Views: 9636

Answers (2)

Moritz
Moritz

Reputation: 559

I too found the documentation on the topic quite limited and examples seem to only show how to navigate an array but not an object. Coming from other databases I was also quite confused that I do not need to cast to json but operate directly on string.

Example Json:

{
  "myKeyA": "Foo",
  "myKeyB": [
    {
      "arrayElementA": "Bar"
    },
    {
      "arrayElementB": "FooBar"
    }
  ]
}

Can be queried like this:

  • SELECT JSON_EXTRACT('...', '$.myKeyA'); returns "Foo"
  • SELECT JSON_EXTRACT('...', '$.myKeyB'); returns [{"arrayElementA": "Bar"}, {"arrayElementB": "FooBar"}]
  • SELECT JSON_EXTRACT('...', '$.myKeyB[1].arrayElementB'); returns "FooBar"

Where the ... would be the json input from which we want to get values.

Upvotes: 5

Bhaskar Choudhary
Bhaskar Choudhary

Reputation: 159

Select JSON_EXTRACT(@json, '$[1]')

Here $ means the JSON Document Tree.

The MySQL reference has better examples. However, be careful while pasting and running code from MySQL to MariaDB as MariaDB has a JSON like text based format which is not native binary JSON. https://dev.mysql.com/doc/refman/8.0/en/json.html

Upvotes: 1

Related Questions