JSON_ARRAY()

MySQL provides this function to return a list of values in array format.

SELECT JSON_ARRAY(1,2,3);
SELECT JSON_ARRAY(1,'cherish','');
SELECT JSON_ARRAY();
SELECT JSON_ARRAY(1,'public',NULL);

Note: Even NULL value can be inserted into the array.

Create a table with a column of data type JSON

CREATE TABLE product_variant (
    id int unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    product_id int unsigned NOT NULL,
    variant_id json NOT NULL
);

To create a column with JSON data type assign it as JSON.

Insert values into table

INSERT INTO product_variant (product_id,variant_id) VALUES (632910392,'[808950810,1070325030]');
INSERT INTO product_variant (product_id,variant_id) VALUES (1071559591,'[1070325040,1070325040]');
INSERT INTO product_variant (product_id,variant_id) VALUES (1071559589,'[1070325038,1055547193]');

Select operations on table

Select all data from the table

Extract elements from JSON Array

On the basis of index.

SELECT JSON_EXTRACT(variant_id,'$[1]') FROM product_variant;

SET @json = '
{ 
    "products" : 
    [
        { 
            "id": 1,  
            "sizes": [ "S", "M", "L" ] 
        },
        { 
            "id": 2, 
            "sizes": [ "EU44", "EU45.5", "EU47" ] 
        },
        { 
            "id": 3, 
            "sizes": [ "LowArch", "MediumArch", "HighArch" ] 
        }
    ]
}
';

SELECT JSON_EXTRACT(@json,'$.products[*].sizes[2]');

Note: [*] is used to select all the elements from the array.

Select JSON_TYPE

SELECT JSON_TYPE(variant_id) FROM product_variant;

JSON_ARRAYAGG()

It is an aggregator function to group all the values into an array.

We have the following table named product_variant_2 :

SELECT product_id,JSON_ARRAYAGG(variant_id) FROM product_variant_2 GROUP BY product_id;

We have selected all product_ids where all the variant_ids are grouped in the form of an array on the basis of product_id.

You've done it! Good Job!

Now you have complete knowledge of JSON_ARRAY(). Try to do hands-on practice with it to gain full confidence.