Question 171261 in Mysql Question posted in
The official MySQL documentation can be found here.

Can MySQL v5 extract multiple occurrences from strings?

Folks!

I need some ideas, with the follow problem:

I have a table in MySQL v5 that stores a string in a field, like the following:

{"content":{"serviceResponse":{"state":{"code":"04","gloss":"CONTINUE"},"products":[{"product":{"productCode":"1" ,"subProductCode":"1"},"productAmount":{"amountGranted":1100000},"groupRisk":{"riskGroup":"A2"},"additionalinformation":{"franchiseTC":"3"," percentAdvance":"70.0"}},{"product":{"productCode":"2"},"groupRisk":{}},{"product":{"productCode":"7"}}]," client":{"newClient":"false","riskGroup":{"riskGroupCode":"A2"}}}}

I need a MySQL v5 (version is important) SQL query that will extract all the values of the occurrences of the "productCode" key from the above string and concatenate them into a new column:

+----------------------------------------------------------------------------+------------+
| content                                                                    | products   |
+----------------------------------------------------------------------------+------------+
| "content":{"serviceResponse":{"state":{"code":"04","gloss":"CONTINUE"}...  | 1,2,7      |
| "content":{"serviceResponse":{"state":{"code":"03","gloss":"CONTINUE"}...  | 1,2        |
| "content":{"serviceResponse":{"state":{"code":"06","gloss":"CONTINUE"}...  | 3,4,5      |
| "content":{"serviceResponse":{"state":{"code":"08","gloss":"CONTINUE"}...  | 1,2,3,5,7  |

From now thank you very much guys!

Tags:

2

Answers


  1. 0

    As I undrestand each column is in separate table , maybe this query can help :

    SELECT
  t1.content,
  GROUP_CONCAT(JSON_EXTRACT(t2.JSON_String, '$.products[*].product.productCode')) AS products
FROM
  table_1 t1
JOIN
  table_2 t2 ON t1.id = t2.id
GROUP BY
  t1.content;
    Login or Signup to reply.
  2. 0 
    create table mytable (id serial primary key, content json);

insert into mytable set content = ...your example JSON...;

select json_extract(content,
  '$.content.serviceResponse.products[*].product.productCode') as list 
from mytable;

    I had to correct your example JSON string, to add one more final } character. In other words, it isn’t valid JSON, and it is rejected by MySQL’s JSON data type and JSON functions.

    Dbfiddle using MySQL 5.7

    Output:

    +-----------------+
| list            |
+-----------------+
| ["1", "2", "7"] |
+-----------------+

    You said in a comment that you were almost ready to use Python instead of SQL, but this SQL solution at least gets you closer to the result you want.

    This won’t work if your MySQL version is 5.6. That version doesn’t support any JSON functions, and it has been out of service for a few years. If you’re still using a host with MySQL 5.6 (or older), you should have upgraded a long time ago.

    You should be thinking of moving to a server that is upgraded to MySQL 8.0. MySQL 5.7 is due to reach its end of service in October 2023, which is just a few months away as we write this.

    If you cannot upgrade to MySQL 8.0 in the foreseeable future, you should reconsider using JSON in MySQL. Use normal rows and columns.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search