Jinsi ya Kushughulikia Data ya Orodha katika MySQL kwa Kutumia JSON (Mwongozo Kamili na Mifano)

1. Utangulizi

Hitaji la Kushughulikia Data ya Safu katika MySQL

Hifadhidata kwa kawaida huhifadhi data kulingana na kanuni za muundo wa uhusiano. Hata hivyo, kulingana na mahitaji ya programu, kuna hali ambapo unaweza kutaka kuhifadhi thamani nyingi katika safu moja. Katika hali kama hizo, muundo wa data unaofanana na “safu” unakuwa wa manufaa.

Kwa mfano, fikiria hali zifuatazo:

• Kuhifadhi lebo nyingi zilizochaguliwa na mtumiaji.
• Kuhifadhi URL nyingi za picha za bidhaa.
• Kuchanganya historia au logi katika uga mmoja.

Faida za Kutumia Aina ya JSON

MySQL haina aina ya “safu” ya moja kwa moja, lakini kwa kutumia aina ya JSON, unaweza kushughulikia miundo ya data inayofanana na safu. Aina ya JSON ni nyorosho sana na inatoa faida zifuatazo:

• Inasaidia miundo ya data iliyopachikwa.
• Inaruhusu usindikaji rahisi wa data ndani ya maswali.
• Inaruhusu usimamizi wa muundo mbalimbali wa data katika uga mmoja.

Katika makala hii, tutatoa utangulizi wa jinsi ya kushughulikia data ya safu kwa ufanisi katika MySQL kwa kutumia aina ya JSON.

2. Maarifa ya Msingi ya Kushughulikia Safu kwa MySQL JSON

JSON ni Aina ya Data Nini?

JSON (JavaScript Object Notation) ni muundo wa kubadilishana data wa nyepesi na rahisi. Katika MySQL, usaidizi wa JSON wa asili ulianzishwa katika toleo 5.7 na baadaye, ukikuruhusu kuhifadhi na kushughulikia data iliyopangwa kwa JSON moja kwa moja ndani ya hifadhidata.

Mfano: Hapo chini ni mfano wa data ambayo inaweza kuhifadhiwa katika safu ya JSON.

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

Faida na Matumizi ya Aina ya JSON

Faida kuu za kutumia aina ya JSON ni kama ifuatavyo:

1. Muundo wa Data Nyorosho : Unaweza kushughulikia data yenye urefu unaobadilika bila kubadilisha muundo wa uhusiano.
2. Usindikaji wa Data kwa Ufanisi : Unaweza kushughulikia data kwa urahisi kwa kutumia kazi maalum za MySQL (kwa mfano, JSON_EXTRACT, JSON_ARRAY ).
3. Muundo Bila Schema : Hakuna haja ya kubadilisha schema mara kwa mara wakati mahitaji ya programu yanabadilika.

Mifano ya matumizi:

• Kuweka kategoria nyingi kwenye taarifa za bidhaa.
• Kuhifadhi mipangilio maalum ya mtumiaji.
• Kutumia data ya JSON iliyopachikwa katika programu za wavuti.

3. Operesheni za Msingi za Safu ya JSON

Kuunda Safu ya JSON

Katika MySQL, unaweza kwa urahisi kuunda safu ya JSON kwa kutumia kazi ya JSON_ARRAY. Safu ni muhimu wakati wa kuhifadhi thamani nyingi katika safu moja.

Mfano

Swali lifuatalo linaunda safu ya JSON iitwayo tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Matokeo:

["PHP", "MySQL", "JavaScript"]

Mfano wa Kitaalamu

Mfano ufuatao unaonyesha jinsi ya kuhifadhi safu ya JSON katika hifadhidata kwa kutumia tamko la INSERT.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Kutoa Data kutoka Safu ya JSON

Ili kurejesha data iliyohifadhiwa katika safu ya JSON, tumia kazi ya JSON_EXTRACT. Kazi hii inakuwezesha kutoa kwa urahisi vipengele maalum kutoka safu.

Mfano

Mfano ufuatao unarejesha kipengele cha pili katika safu (kielelezo kinaanza kwa 0).

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Matokeo:

"MySQL"

Kurejesha Vipengele Vingi

Unaweza pia kurejesha vipengele vingi kwa wakati mmoja.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Kuongeza, Kusasisha, na Kuondoa Data

Kuongeza Data kwenye Safu

Unaweza kutumia kazi ya JSON_ARRAY_APPEND kuongeza data mpya kwenye safu iliyopo.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Matokeo:

["PHP", "MySQL", "JavaScript"]

Kusasisha Data katika Safu

Unaweza kusasisha kipengele maalum katika safu kwa kutumia kazi ya JSON_SET.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Matokeo:

["PHP", "Python", "JavaScript"]

Kuondoa Data kutoka kwenye Safu

Unaweza kuondoa kipengele maalum kutoka kwenye safu kwa kutumia kazi ya JSON_REMOVE.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Matokeo:

["PHP", "JavaScript"]

4. Kutafuta na Kuchuja Safu za JSON

Kutafuta Safu Zinazobeba Data Maalum

Ili kuangalia ikiwa safu ya JSON ina data maalum, tumia kazi ya JSON_CONTAINS. Kazi hii inaamua ikiwa thamani iliyobainishwa ipo ndani ya safu ya JSON.

Mfano

Mfano ufuatao unakagua ikiwa safu ya JSON ina “MySQL.”

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Matokeo:

1  (if present)
0  (if not present)

Mfano wa Kitaalamu: Utafutaji wa Masharti

Ili kutafuta safu zilizo na thamani maalum katika safu ya JSON ndani ya jedwali la hifadhidata, tumia JSON_CONTAINS katika kifungu cha WHERE.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

Swali hili linarejesha safu ambapo safu ya tags ina “MySQL.”

Kupata Urefu wa Safu

Ili kupata idadi ya vipengele katika safu ya JSON, tumia kazi ya JSON_LENGTH. Kazi hii inarudisha idadi ya vipengele katika safu na ni muhimu kwa uchambuzi wa data na mantiki ya masharti.

Mfano

Mfano ufuatao unapata idadi ya vipengele katika safu.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Matokeo:

3

Mfano wa Kitaalamu: Kuchukua Safu Zinazokidhi Sharti Maalum

Ili kuchukua safu ambapo idadi ya vipengele ni kubwa kuliko au sawa na thamani maalum, tumia JSON_LENGTH katika kifungu cha WHERE.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

Swali hili linarejesha safu ambapo safu ya tags ina vipengele viwili au zaidi.

Mfano wa Swali la Masharti ya Juu

Unaweza kuchanganya masharti mengi kwa utafutaji wa hali ya juu. Swali lifuatalo linatafuta safu ambapo safu ya tags ina “JavaScript” na ina vipengele vitatu au zaidi.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Mifano ya Kitaalamu: Kutumia Safu za JSON katika Matukio Halisi

Jinsi ya Kuhifadhi Kategoria za Bidhaa kama Safu ya JSON

Katika tovuti za e-commerce na mifumo inayofanana, bidhaa inaweza kuwa na kategoria nyingi. Katika hali kama hizo, unaweza kuhifadhi taarifa za kategoria kwa ufanisi kwa kutumia safu ya JSON.

Mfano: Kuhifadhi Taarifa za Kategoria za Bidhaa

Hapo chini ni mfano wa kuunda safu ya JSON iitwayo categories katika jedwali la bidhaa na kuhifadhi kategoria nyingi.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

Muundo huu unahifadhi data kwa ufupi hata wakati bidhaa ina kategoria nyingi.

Swali la Kuchukua Bidhaa katika Kategoria Maalum

Kwa kutumia aina ya data ya JSON, unaweza kutafuta kwa urahisi bidhaa ambazo ziko katika kategoria maalum.

Mfano wa Swali

Swali lifuatalo linarejesha bidhaa zote katika kategoria ya “Electronics”.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Matokeo:

Laptop
Smartphone

Swali hili hufanya iwe rahisi kurejesha orodha za bidhaa kwa kategoria kwa njia inayobadilika.

Mfano: Kuchuja kwa Safu ya Bei

Hebu tazame jinsi ya kuhifadhi data ya JSON inayojumuisha taarifa za bei kisha kutafuta bidhaa kulingana na safu ya bei.

Data ya Mfano

The following example stores price information per product using the JSON type.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Query Example

To search for products priced at 100,000 or higher, use the JSON_EXTRACT function.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Result:

Laptop

Expanding JSON with JSON_TABLE and Query Example

If you want to query JSON data in a relational format, the JSON_TABLE function is very useful. This function allows you to expand a JSON array into a virtual table.

Example

The following example expands a JSON array and displays each category as a separate row.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Result:

category_name
--------------
Electronics
Computers
Mobile Devices

6. Important Considerations When Using the JSON Data Type

Performance Optimization Tips

While the JSON type is flexible, poor design can negatively impact database performance. Below are key optimization points.

1. Using Indexes

In MySQL, you cannot create an index directly on a JSON column itself, but you can create a generated column and index a specific key.

Example: Creating an Index Using a Generated Column

In the following example, the price key inside JSON data is used as an index target.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

By using a generated column, you can significantly improve search performance on JSON data.

2. Avoid Overly Complex JSON Structures

Deeply nested JSON structures can reduce query readability and performance. When designing data, choose the simplest JSON structure possible.

Good example:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Example to avoid:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

How to Leverage Indexes

When indexing using generated columns, keep the following points in mind:

1. The generated column must be STORED .
2. Use the JSON_EXTRACT function to extract a specific key as a generated column.

For example, to extract the first element of the categories key and create an index, do the following.

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

The Importance of Data Validation

Because JSON data is flexible, it is also easier to store data in the wrong format. To maintain data integrity, use the following approaches.

1. Use CHECK Constraints

In MySQL 8.0 and later, you can validate JSON structure and content using CHECK constraints.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Application-Level Validation

When inserting data, it is recommended to validate JSON format on the application side. Programming languages such as PHP and Python can validate JSON using their standard libraries.

7. Frequently Asked Questions About Using Arrays in MySQL

Q1: Does MySQL have an array data type?

A1: MySQL haina aina ya data ya “array” moja kwa moja. Hata hivyo, kwa kutumia aina ya JSON, unaweza kushughulikia miundo ya data inayofanana na array. Kwa kutumia aina ya JSON, unaweza kuhifadhi thamani nyingi katika safu moja na kuzibadilisha kupitia maswali.

Example:

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

Result:

["Value 1", "Value 2", "Value 3"]

Q2: Can you create an index on JSON data?

A2: Huwezi kuunda faharasa moja kwa moja kwenye aina ya JSON yenyewe. Hata hivyo, unaweza kutoa ufunguo au thamani maalum kwenye safu iliyozalishwa na kuunda faharasa kwenye safu hiyo iliyozalishwa.

Example:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

This allows you to efficiently search values inside JSON data.

Q3: Is there a size limit for JSON data?

A3: Aina ya JSON ya MySQL inaweza kuhifadhi hadi 4GB ya data. Hata hivyo, kutumia nyaraka za JSON kubwa sana kunaweza kupunguza utendaji, hivyo unapaswa kubuni data yako kwa uangalifu.

Recommendations:

• Store only the minimum required data. → Hifadhi tu data ya chini kabisa inayohitajika.
• Avoid deeply nested JSON structures. → Epuka miundo ya JSON iliyopangwa kwa kina.

Q4: How can I update a specific element inside a JSON array?

A4: Unaweza kusasisha kipengele maalum katika array kwa kutumia kazi ya JSON_SET.

Example:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Result:

["PHP", "Python", "JavaScript"]

Q5: Comparing the JSON Type vs. Normal Table Design

A5: Ingawa aina ya JSON ni rahisi sana, ina sifa tofauti ikilinganishwa na muundo wa kawaida wa hifadhidata ya uhusiano.

8. Summary

Benefits of Using the JSON Data Type for Array Operations in MySQL

In this article, we explained the JSON data type, which is helpful when working with array-like data in MySQL. Below is a summary of the key points covered:

1. Why Use the JSON Type MySQL does not have a direct array type, but by using the JSON type, you can store multiple values in one column and achieve flexible data manipulation.
2. Basic JSON Operations

• We covered how to create JSON arrays, extract data, update data, and remove data.
• By using convenient functions such as JSON_ARRAY , JSON_EXTRACT , and JSON_SET , you can efficiently manipulate array data.

1. Search and Filtering

• How to search for data containing specific values using JSON_CONTAINS .
• How to retrieve the number of elements with JSON_LENGTH and perform conditional filtering.

1. Practical Examples Through real-world use cases such as managing product categories and filtering by price, we learned concrete ways to apply JSON arrays in applications.
2. Considerations and Optimization

• We explained how to set up indexing using generated columns and emphasized the importance of validating JSON data.

Next Steps When Using the JSON Data Type

By using the JSON type in MySQL, you can manage data more flexibly than in traditional relational database designs. However, good design and performance considerations are essential.

Topics to Learn Next:

• Using Composite Indexes Index design that combines JSON data with normal columns.
• Using Advanced JSON Functions Perform more complex operations with functions such as JSON_MERGE and JSON_OBJECT .
• Application-Level Data Handling How to efficiently manipulate MySQL JSON data using PHP or Python.

Summary

Through this article, you should now understand how to efficiently handle array-like data using MySQL’s JSON data type. By applying this knowledge, you can design more flexible and scalable databases.