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 · Accepted Answer · 2021-06-01 13:52:39Z

1

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

edited Jun 1, 2021 at 13:52

answered Jun 1, 2021 at 13:32

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Harikrishna Thummalapelly Over a year ago

COALESCE function is not working for mysql server. Is there any alternative function for this please?

Akina Over a year ago

@HarikrishnaThummalapelly Well, conservative solution provided.

Harikrishna Thummalapelly Over a year ago

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?

Akina Over a year ago

@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.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related