0

I work in mysql 8 and have a problem on counting data in json format field. This is my table:

enter image description here

I want to count data in absensi field where the key is "657" and the value is "0". So, by this table it must give me result 4.

I tried to use JSON_EXTRACT(absensi, '$.657') but always give me some error [42000][3143] Invalid JSON path expression. The error is around character position 6.

Can you help me how to solved this problem?

Thank's in advance...

Adi Sparta
  • 525
  • 2
  • 7
  • 23

1 Answers1

1

Your key value is a string. Treat it as string instead of integer.

select json_extract(absensi, '$."657"')

If you are using your field as key value, you can build the parameter using concat() function.

select json_extract(absensi, concat('$."', fieldA, '"')) from test;

see dbfiddle.

Ed Bangga
  • 12,879
  • 4
  • 16
  • 30
  • Omg, sorry my bad. Thank for your answer. But can we using another field to extract it? like json_extract(absensi, field_name) so I can using it in subquery – Adi Sparta Jan 13 '20 at 02:02
  • @AdiSparta, please provide sample data and update your question. – Ed Bangga Jan 13 '20 at 02:03
  • @AdiSparta . . . Or better yet, ask a *new* question, with sample data as *text* in the question and perhaps a db/SQL fiddle. – Gordon Linoff Jan 13 '20 at 02:07
  • @EdBangga thanks, your updated answer was what I wanted. I will used your second query from my problem. Thanks – Adi Sparta Jan 13 '20 at 02:12