25 Generation of JSON Data Using SQL

You can use SQL to generate JSON objects and arrays from non-JSON data in the database. For that, use either constructor JSON or SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg.

Note:

Besides generating JSON data from relational data explicitly, you can define a JSON-relational duality view, which automatically generates JSON documents from data in relational tables. Updating the documents supported (generated) by a duality view automatically updates the underlying relational data. Dually, updating data in the underlying tables automatically updates the documents supported by the view. See Overview of JSON-Relational Duality Views in JSON-Relational Duality Developer's Guide.

  • Overview of JSON Generation
    An overview is presented of JSON data generation: best practices, the SQL/JSON generation functions, a simple JSON constructor syntax, handling of input SQL values, and resulting generated data.
  • Handling of Input Values For SQL/JSON Generation Functions
    The SQL/JSON generation functions take SQL values as input and return a JSON object or array. The input values are used to produce JSON object field–value pairs or JSON array elements. How the input values are used depends on their SQL data type.
  • SQL/JSON Function JSON_OBJECT
    SQL/JSON function json_object constructs JSON objects from the results of evaluating its argument SQL expressions.
  • SQL/JSON Function JSON_ARRAY
    SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions.
  • SQL/JSON Function JSON_OBJECTAGG
    SQL/JSON function json_objectagg constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.
  • SQL/JSON Function JSON_ARRAYAGG
    SQL/JSON function json_arrayagg constructs a JSON array by aggregating information from multiple rows of a grouped SQL query as the array elements. The order of array elements reflects the query result order, by default, but you can use the ORDER BY clause to impose array element order.
  • Read-Only Views Based On JSON Generation
    You can create read-only views using JSON generation functions or constructor JSON. Anyone with access can use the views as if they were read-only tables. Users of the JSON data need not know or care whether it is stored as such or generated as needed.

Parent topic: Generation of JSON Data