MySQL Extract values from JSON type

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Introduction

MySQL 5.7.8+ supports native JSON type. While you have different ways to create json objects, you can access and read members in different ways, too.

Main function is JSON_EXTRACT, hence -> and ->> operators are more friendly.

Syntax

  • JSON_EXTRACT(json_doc,path[,...])
  • JSON_EXTRACT(json_doc,path)
  • JSON_EXTRACT(json_doc,path1,path2)

Parameters

ParameterDescription
json_docvalid JSON document
pathmembers path

Remarks

Mentioned in MySQL 5.7 Reference Manual

  • Multiple matched values by path argument(s)

If it is possible that those arguments could return multiple values, the matched values are autowrapped as an array, in the order corresponding to the paths that produced them. Otherwise, the return value is the single matched value.

  • NULL Result when:
    • any argemunt is NULL
    • path not matched

Returns NULL if any argument is NULL or no paths locate a value in the document.



Got any MySQL Question?