FIELD()

Documentation

Home » Documentation » Using VoltDB

FIELD()

FIELD() — Extracts a field value from a JSON-encoded string column.

Synopsis

FIELD( column, field-name )

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"}
}

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 FIELD() function only retrieves first-level fields in the JSON object. However, it is possible to delve deeper into the JSON structure by nesting instances of the FIELD function, like so:

SELECT * FROM Authors WHERE 
  FIELD( FIELD(profile,'description'),'period') = 'Victorian';

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

  • If the requested field name does not exist, the function returns a null value.

  • The first argument to the FIELD() function must be a valid JSON-encoded string. However, the content is not evaluated until the function is invoked at runtime. Therefore, it is the responsibility of the database application to ensure the validity of the content. If the FIELD() function encounters invalid content, the query will fail.

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;