Extract a key from list of key,values pairs in json field mysql

Question

I have a data in the following format:

"article_body" : [
        {
            "article_desc" : "THURSDAY, Sept. 1, 2016 (HealthDay News) -- Dapagliflozin improves insulin sensitivity and increases lipid oxidation and plasma ketone concentration in patients with type 2 diabetes mellitus (T2DM), according to a study published online Aug. 25 in Diabetes Care. \n\n Giuseppe Daniele",
            "links" : [{
                    "link_name" : "Full Text (subscription or payment may be required)"}
            ]}
    ],

I want to extract the key

article_desc

from article_body.

My Snippet of code in Mysql:

SELECT 

    JSON_EXTRACT(full_article_json, '$.article_body."article_desc"') AS description,
FROM
    wc_article_full_data;

I'm getting null data, how to parse such data?

Answer 1

Your key = 'article_body' is an array JSON, so you need to use index get the data.

You can try this.

Schema (MySQL v5.7)

CREATE TABLE wc_article_full_data(

   full_article_json JSON
);

insert into wc_article_full_data values (
'{"article_body" : [
    {
        "article_desc" : "THURSDAY, Sept. 1, 2016 (HealthDay News) -- Dapagliflozin improves insulin sensitivity and increases lipid oxidation and plasma ketone concentration in patients with type 2 diabetes mellitus (T2DM), according to a study published online Aug. 25 in Diabetes Care.  Giuseppe Daniele",
        "links" : [{
                "link_name" : "Full Text (subscription or payment may be required)"}
        ]}
]}');

---

Query #1

SELECT JSON_EXTRACT(full_article_json,'$.article_body[0].article_desc') AS descriptio
FROM wc_article_full_data;

| descriptio                                                                                                                                                                                                                                                                                 |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| "THURSDAY, Sept. 1, 2016 (HealthDay News) -- Dapagliflozin improves insulin sensitivity and increases lipid oxidation and plasma ketone concentration in patients with type 2 diabetes mellitus (T2DM), according to a study published online Aug. 25 in Diabetes Care.  Giuseppe Daniele" |

---

View on DB Fiddle

If you want to get all value from article_desc which from article_body array. you can try to use* in index.

SELECT JSON_EXTRACT(full_article_json,'$.article_body[*].article_desc') AS descriptio
FROM wc_article_full_data

Answer 2

use JSON_KEYS

eg.

SELECT JSON_KEYS(full_article_json) as jsonKeys;

it will return all the keys from the json array