0

I have a table in MySQL where each row contains JSON returned from another system. The JSON will look something like:

[{"userId": "Dave"},{"userId": "Mary", "errorCode" : "DB Fail"}, {"userId": "Lorenza", "errorCode": "Web Error"}]

and I'm only interested in the members of the array containing an error code. In the future, these will be parsed into seperate rows of their own table, but in the meantime does MySql offer a way to extract only these with an errorCode?

I can use JSON_EXTRACT to extract the errorCodes only

JSON_EXTRACT(jsonData, '$[*].errorCode') AS errorCodes

but I really want the rest of the member (userId in the example above)

matt freake
  • 4,877
  • 4
  • 27
  • 56
  • Something similar to your requirement, mentioned here: https://stackoverflow.com/questions/39906435/convert-json-array-in-mysql-to-rows – Lovepreet Singh Jun 06 '18 at 11:57

2 Answers2

0

You could use the JSON_CONTAINS function to find the records with errorCode and then then use JSON_EXTRACT on those records. Put the JSON_CONTAINS in the where clause

Dave Stokes
  • 775
  • 4
  • 10
0

I don't think you could do this with a single query without known boundaries of the number of elements, but you could use a stored procedure to run a loop.

e.g. each iteration runs LOCATE to find the position of "errorCode", and uses that location to run SUBSTR and/or SUBSTRING_INDEX to get the userid value and append it to another variable. The looped variable would just be the offset used in the LOCATE query.

Bobert1234
  • 130
  • 12