FIELD()

Documentation

VoltDB Home » Documentation » Using VoltDB

FIELD()

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

Synopsis

FIELD( column, field-name-path )

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:

  • 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;