Description

The FIELD() function extracts a field value from a JSON-encoded string. For example, assume the VARCHAR column Profile contains the following JSON string:

{"first":"Charles","last":"Dickens","birth":1812,
 "description":{"genre":"fiction",
                "period":"Victorian",
                "output":"prolific",
                "children":["Charles","Mary","Kate","Walter","Francis",
                            "Alfred","Sydney","Henry","Dora","Edward"]
               }
}

It is possible to extract individual field values using the FIELD() function, as in the following SELECT statement:

SELECT FIELD(profile,'first') AS firstname, 
       FIELD(profile,'last') AS lastname FROM Authors;

It is also possible to find records based on individual JSON fields by using the FIELD() function in the WHERE clause. For example, the following query retrieves all records from the Authors table where the JSON field birth is 1812. Note that the FIELD() function always returns a string, even if the JSON type is numeric. The comparison must match the string datatype, so the constant '1812' is in quotation marks:

SELECT * FROM Authors WHERE FIELD(profile,'birth') = '1812';

The second argument to the FIELD() function can be a simple field name, as in the previous examples. In which case the function returns a first-level field matching the specified name. Alternately, you can specify a path representing a hierarchy of names separated by periods. For example, you can specify the genre element of the description field by specifying "description.genre" as the second argument, like so

SELECT * FROM Authors WHERE 
  FIELD(profile,'description.genre') = 'fiction';

You can also use array notation — with square brackets and an integer value — to identify array elements by their position. So, for example, the function can return "Kate", the third child, by using the path specifier "description.children[2]", where "[2]" identifies the third array element because JSON arrays are zero-based.

Two important points to note concerning input to the FIELD() function:

Example

The following example uses the FIELD() function to both return specific JSON fields within a VARCHAR column and filter the results based on the value of a third JSON field:

SELECT product_name, sku, 
       FIELD(specification,'color') AS color,
       FIELD(specification,'weight') AS weight FROM Inventory 
   WHERE FIELD(specification, 'category') = 'housewares' 
   ORDER BY product_name, sku;