How to extract values from column of mysql table having partial Json array values and remaining with string values

Question

Names of these 3 columns were Date, TargetName, Amount. TargetName column has partial rows with json array values and remaining rows with strings.

I want to extract value[0] from json array. With condition like [case when (targetName is Json type) Then $.value[0] else targetName] How to apply this condition and get the expected result?

Expected outcome: TargetName

SSC Stenographer

UP Police Sub Inspector

UPRVUNL JE

AAI JE Technical

ACC Exam

Akina · Answer 1 · 2021-06-01T13:52:39.750

1

SELECT CASE WHEN JSON_VALID(TargetName)
            THEN TargetName ->> "$[0].value"
            ELSE TargetName
            END AS TargetName ,
       ...

edited Jun 01 '21 at 13:52

answered Jun 01 '21 at 13:32

Akina

39,301
5
14
25

COALESCE function is not working for mysql server. Is there any alternative function for this please? – Harikrishna Thummalapelly Jun 01 '21 at 13:45
1

@HarikrishnaThummalapelly Well, conservative solution provided. – Akina Jun 01 '21 at 13:53
This is almost sorted. But these target names are stored as strings like "[{'language': 'English', 'value': 'West Bengal Group D'}, {'language': 'Hindi', 'value': 'West Bengal Group D'}]" How to deal with this? – Harikrishna Thummalapelly Jun 01 '21 at 14:09
@HarikrishnaThummalapelly Provide a fiddle (or CREATE TABLE + INSERT INTO, 3-5 rows with and without JSON **as formatted code**) and desired output, not as screenshot, into your question. And show desired output for provided source data. – Akina Jun 01 '21 at 16:02

How to extract values from column of mysql table having partial Json array values and remaining with string values

1 Answers1