Skip to main content
Version: 7.0

JSON operator

The JSON and JSONTEXT operators create a property that builds JSON from a list of specified properties or, in the general case, from a form.

Syntax​

The operator has two forms, both starting with the jsonKeyword choice. The form that builds JSON from a list of properties:

jsonKeyword FROM [columnId1 =] propertyExpr1, ..., [columnIdN =] propertyExprN
  [WHERE whereExpr]
  [ORDER orderExpr1 [DESC], ..., orderExprL [DESC]]
  [TOP topExpr] [OFFSET offsetExpr]

The form that builds JSON from a form:

jsonKeyword ( formName [OBJECTS objName1 = expr1, ..., objNameK = exprK]
  [FILTERS filterExpr1, ..., filterExprP]
  [TOP topSelect] [OFFSET offsetSelect] )

Where jsonKeyword is defined as:

JSON
JSONTEXT

And topSelect and offsetSelect are each defined either as a single expression or as a per-group-object map:

topExpr
topGroupId1 = topPropertyExpr1, ..., topGroupIdT = topPropertyExprT
offsetExpr
offsetGroupId1 = offsetPropertyExpr1, ..., offsetGroupIdF = offsetPropertyExprF

Description​

The JSON and JSONTEXT operators create a property that builds JSON either from a list of properties (the FROM form) or from a form. The result is produced in a structured view: building JSON from a list of properties is a special case of building JSON from a form, so the meaning of column names, the object group hierarchy, and the resulting structure follow the form export behavior. Unlike the EXPORT operator, which is an action that writes the result to a file, this operator is an expression: it returns the built JSON as the property value.

The difference between the two keywords is the class of the returned value. JSON returns a value of the JSON class (a file holding the JSON document), while JSONTEXT returns a value of the JSONTEXT class (the JSON document as text).

When building JSON from a form, the OBJECTS block fixes form objects to given values: each such object is constrained to equal the value passed and does not participate in building the object group hierarchy. The FILTERS block adds further filter conditions to the form before the build.

Parameters​

  • jsonKeyword

    The keyword choice selecting the class of the result. JSON builds a value of the JSON class (a file), JSONTEXT builds a value of the JSONTEXT class (text).

  • propertyExpr1, ..., propertyExprN

    A non-empty list of expressions from whose values the JSON is built. Each expression corresponds to a column of the result.

  • columnId1, ..., columnIdN

    A list of column IDs in the resulting JSON into which data from the corresponding property will be written. Each list element is either a simple ID or a string literal. If no ID is specified, it is considered equal to expr<column number> by default.

  • whereExpr

    An expression whose value is the condition of the build. If not specified, it is considered equal to the disjunction of all the listed properties (that is, at least one of the properties must be non-NULL).

  • orderExpr1, ..., orderExprL

    A list of expressions by which the built data is sorted. Only properties present in the list propertyExpr1, ..., propertyExprN can be used.

  • DESC

    Keyword. Specifies the reverse sort order for the preceding expression. By default, ascending sort is used.

  • formName

    The name of the form from which the JSON is built. Composite ID.

  • objName1 ... objNameK

    Names of form objects for which fixed values are specified. Simple IDs.

  • expr1 ... exprK

    Expressions whose values determine the fixed values for the form objects.

  • filterExpr1, ..., filterExprP

    A list of expressions used as extra filters added to the form before the build.

  • topExpr

    Expression whose value limits the build to its first records.

  • topGroupId1 ... topGroupIdT

    Form object group simple IDs for which the limit is set per group object instead of for the whole build.

  • topPropertyExpr1 ... topPropertyExprT

    Expressions whose values limit the records of the corresponding group objects.

  • offsetExpr

    Expression whose value skips that many leading records of the build.

  • offsetGroupId1 ... offsetGroupIdF

    Form object group simple IDs for which the offset is set per group object instead of for the whole build.

  • offsetPropertyExpr1 ... offsetPropertyExprF

    Expressions whose values set the offset of the corresponding group objects.

Examples​

// builds JSON from a list of properties
MESSAGE JSON FROM code = '1', message = 'OK';
FORM testF
      OBJECTS j = INTEGER
      PROPERTIES ab = '34'
      OBJECTS i = INTEGER
      PROPERTIES name = 'Name ' + (i AS INTEGER)
;

run() {
    // builds JSON from a form, fixing object j and adding a filter on object i
    MESSAGE JSON (testF OBJECTS j = 4 FILTERS mod(i, 2) = 0);
}