Select data from json object using mysql

Question

I have a table containg two columns:

Customer id
1. json_data (it contains JSON object) is as follow

{
  "nameValuePairs": {
    "CONTACTS": {
      "nameValuePairs": {
        "contacts": {
          "values": [
            {
              "nameValuePairs": {
                "contact_id": "1",
                "contact_phoneNumber": "080000016",
                "contact_phoneNumberCategory": "Mobile",
                "contact_firstName": "Huawei Customer Service",
                "contact_last_name": "Huawei Customer Service",
                "contact_title": "Huawei Customer Service",
                "contact_email": "mobile.pk@huawei.com"
              }
            },
            {
              "nameValuePairs": {
                "contact_id": "2",
                "contact_phoneNumber": "15",
                "contact_phoneNumberCategory": "Mobile",
                "contact_firstName": "Police Helpline",
                "contact_last_name": "Police Helpline",
                "contact_title": "Police Helpline"
              }
            },
            {
              "nameValuePairs": {
                "contact_id": "3",
                "contact_phoneNumber": "16",
                "contact_phoneNumberCategory": "Mobile",
                "contact_firstName": "Fire Brigade Helpline",
                "contact_last_name": "Fire Brigade Helpline",
                "contact_title": "Fire Brigade Helpline"
              }
            }
          ]
        }
      }
    }
  }
}

Now I want to extract information using MySQL such that I get the table such that

Customer id	contact_title
1	Huawei helpline
1	Police

How can I get above table? I need query

I try this query

JSON_EXTRACT(json_data, '$.nameValuePairs.CONTACTS.nameValuePairs.contacts.values[0]
                          .nameValuePairs.contact_title') AS "Contact name",

I am getting this table

customer_id	contact_title
1	['Huawei helpline', 'Police', 'Fire Brigade']
2	['Huawei helpline', 'Police', 'Fire Brigade']

I don't want this table. I want one contact_title per row against the same customer_id

I don't see any way you could get that result with your query. You would only get that if you used `JSON_ARRAYAGG()`. — Barmar, Jan 26 '23 at 04:18
*I want one contact_title per row against same customer id* https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html — Akina, Jan 26 '23 at 04:53
@BarbarosÖzhan I am using MySQL . Can you solve this problem? — Muhammad Abdullah Khan, Jan 26 '23 at 07:32
@BarbarosÖzhan can we use two or more JSON_Table() function because I have more than 2 columns. — Muhammad Abdullah Khan, Jan 26 '23 at 07:51

Barbaros Özhan · Answer 1 · 2023-01-26T08:41:07.920

If the DB version is 8.0+, then you can use JSON_TABLE() function along with a cross join such as

SELECT contact_id, contact_title
  FROM `tab`
  JOIN JSON_TABLE(
                 `json_data`,
                 '$.nameValuePairs.CONTACTS.nameValuePairs.contacts.values[*]
                   .nameValuePairs' 
                  COLUMNS (
                           contact_id VARCHAR(100) PATH '$.contact_id'
                          )
       ) j1
  JOIN JSON_TABLE(
                  `json_data`,
                  '$.nameValuePairs.CONTACTS.nameValuePairs.contacts.values[*]
                    .nameValuePairs' 
                   COLUMNS (
                            contact_title VARCHAR(100) PATH '$.contact_title'
                           )
             ) j2

or a cleaner option might be

WITH t AS
(  
 SELECT contact_id, contact_title
   FROM `tab`
   JOIN JSON_TABLE(
                  `json_data`,
                  '$.nameValuePairs.CONTACTS.nameValuePairs.contacts.values[*]
                    .nameValuePairs' 
                   COLUMNS (
                            contact_id VARCHAR(100) PATH '$.contact_id',
                            contact_title VARCHAR(100) PATH '$.contact_title'
                           )
        ) AS j
)
SELECT t2.contact_id, t1.contact_title
  FROM t AS t1
  JOIN t AS t2

Demo

Select data from json object using mysql

1 Answers1