Questions tagged [mysql-json]

DO NOT USE FOR BASIC JSON. Use for questions about the JSON data type in MySQL 5.7 or later. Use with the [mysql] tag.

Overview

MySQL 5.7.8 introduced a new native json data type.

This data type allows the developer to store and manipulate JSON-encoded data more efficiently, in comparison to JSON data stored as the more common string data type.

Insert

Data inserted must be valid JSON syntax

INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

Select

Retrieving data directly from a JSON column

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

Create table example

The following is a data-definition statement that defines an auto-generated JSON Object that reflects the corresponding values of the data row in the target table.

CREATE TABLE triangle (
  sidea     DOUBLE,
  sideb     DOUBLE,
  sidec     DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)),
  rowdata   JSON AS (JSON_OBJECT("aa",sidea, "bb",sideb, "cc",sidec ))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);    

## Table
1   1   1.4142135623730951  {"aa": 1, "bb": 1, "cc": 1.4142135623730951}
3   4   5   {"aa": 3, "bb": 4, "cc": 5}
6   8   10  {"aa": 6, "bb": 8, "cc": 10}

Basic functions

--- Query
SELECT JSON_Array(
  'alpha'      
  ,'bravo'    
  ,'charlie'
  ,'delta'
) AS Result;          

--- Result          
["alpha", "bravo", "charlie", "delta"]

--- Query
SELECT JSON_OBJECT(
  'alpha',      'one'
  ,'bravo',     'two'
  ,'charlie',   '3'
  ,'delta',     004
) AS Result;

--- Result          
{"alpha": "one", "bravo": "two", "delta": 4, "charlie": "3"}

See also

386 questions
0
votes
1 answer

MySQL - list out all the JSON properties as separate columns

We have unstructured data that is being stored as JSON at MySQL (one of the tables along with Structured data). We would like to extract the data, but we are not sure how to extract the JSON Data as JSON Data could contain any property (no common…
Bhupal
  • 141
  • 1
  • 11
0
votes
1 answer

Count data in mysql json by key

I work in mysql 8 and have a problem on counting data in json format field. This is my table: I want to count data in absensi field where the key is "657" and the value is "0". So, by this table it must give me result 4. I tried to use…
Adi Sparta
  • 525
  • 2
  • 7
  • 23
0
votes
1 answer

JSON_ARRAY_APPEND() reject if target value is null Mysql

Here is Scenario, Adding the user-id's to the user_details. what if external_id does not present in the profile table and it returns null (select statement returns null). How does JSON_ARRAY_APPEND reject the query and does not add to the…
Rahul Reddy
  • 110
  • 11
0
votes
2 answers

Backslash in json_encode (PHP) + JSON_ARRAYAGG (mySQL)

The problem is the backslashes with the quotes in the array colors. I think it's because the JSON_ARRAYAGG but I don't know how to print a correct json. Query: SELECT a.id_product, JSON_ARRAYAGG(c.name_color) as colors, a.url FROM products as a…
Giulia
  • 37
  • 8
0
votes
0 answers

MariaDB aggregated sum of JSON objects with dynamic keys

I'm running MariaDB 10.2.29, and started exploring JSON data types. I'm trying to understand how to group by dynamic JSON keys, and sum up their nested values. For example, the table contains the following in the 'data' field entries: MariaDB…
Andrei
  • 23
  • 1
  • 8
0
votes
1 answer

Select Price field MySQL JSON on Laravel

I want to get data that Price is greater than or equal to a specified value (taken from the input). $minPrice = $request->min_price; $maxPrice = $request->max_price; $value = Dproduct::where(['status' => 1])->where('price', '>',…
Hoang Dinh
  • 157
  • 1
  • 10
0
votes
1 answer

How to query an object of objects in mysql json column

I have an order_summary JSON column in my orders table with the following structure { "total": 16.895, "products": { // products is an object of objects instead of array of objects "98": { "price": "2.400", "quantity": 2, …
Shobi
  • 10,374
  • 6
  • 46
  • 82
0
votes
2 answers

How to insert attribute to json string in mysql?

I had a json string in mysql database like following. {"name":"Georg","position":"Manager"} I need to add another attribute like "date_of_birth":"1989-06-08"
0
votes
0 answers

MySQL JSON: How to check if an object in an array has two keys where the first key value is given

In my table I have a JSON field that has an object with key values where some values are arrays. Verifying if a single key exists or even if the key exists with a specified value is easy enough using JSON_CONTAINS_PATH and JSON_EXTRACT respectively,…
navybofus
  • 59
  • 9
0
votes
1 answer

How to select json array from mysql?

I have such json array in MySql [{ "to": "0xACA7A6819ebc5135D1Be4D9952C1219ad6a3182f", "from": "0xF6b023e576E86067e9604ee31215873E4495C06f"}] I need simple action, just get all array! // This not work SELECT deposit FROM users WHERE wallet…
0
votes
1 answer

MySQL. Wrong order by json fied containing utf-8 string

I have a field title of type json that contains translations for different locales. It looks like {'en'=>'Title', 'uk'=>'Заголовок'} I'm trying to order records by a translation select id, slug, title->>'$.uk' as locale_title from blog_posts order…
Andriy Lozynskiy
  • 2,444
  • 2
  • 17
  • 35
0
votes
2 answers

PHP Query: Set parameter to an Sql key for JSON search

I have a problem here, my code is: $query = $con2->createQueryBuilder()->select('*') ->from('`blog_entry`', 'p') ->where('WHERE url->"$.?" = ?') ->setParameter(0, $request->getLocale()) …
Marc Garcia
  • 1,388
  • 1
  • 9
  • 21
0
votes
1 answer

how to pass a list in to mysql procedure?

Do you want to insert one to many data at once(list of data) in mysql procedure, you can use json method for that. then, you have to pass your list of data as a json by mysql json format. then you can insert list data using while loop.
0
votes
1 answer

Using MySQL JSON_OBJECT() with Parameterized query

I was trying to insert an entry to MySQL table using following query.If I use MySQL parameterized query with mysqli, as below, it works without any issues. insert into notelist(checksum,details,status,location)…
Basil K Y
  • 490
  • 5
  • 8
0
votes
1 answer

Laravel fetching JSON column MariaDB syntax error

Im trying to fetch some datas depending on the JSON column meta. However, something weirds happen around the -> symbol. File::whereJsonContains('meta->serie', 'value')->toSql(); output "select * from `files` where…