Function to Convert String to JSON

parse_json function

The function parse_json converts a string argument to a JSON instance. The input string must be a comma-separated list of one or more name-value pairs.

Syntax:

json parse_json(string)

Semantics:

The input string argument must be a valid JSON text. The parse_json function parses the string and converts it to a JSON object.

For more details on JSON data type in Oracle NoSQL Database data model, see JSON Data Type. An error is displayed if an incorrect JSON text is supplied in the string argument (for example, a missing colon to separate the name and field values).

Example 1: Consider a user data table for a library application. Currently, the subscription details are in a JSON document, which is stored as a string. You want to add them as a JSON object.

To achieve this, consider the following schema for the table:

CREATE TABLE userslib (id LONG GENERATED BY DEFAULT AS IDENTITY, details JSON, Book1 json,
      Book2 Json, Book3 Json, PRIMARY KEY (id))

You can declare the subscription ID as an IDENTITY column. The current subscription details can be included as a JSON object in the details field. Assuming, three books are allowed per subscription at any point, you can update the details of the borrowed books as JSON objects in the corresponding Book fields.

You use the parse_json function to convert the subscription data of a user, which is in a string format to a JSON object and insert it into the JSON field as follows:

mode json -pretty

insert into userslib (details) values (parse_json("{\"firstName\":\"John\",\"lastName\":\"Smith\",\"DOB\":\"22-2-1995\",\"address\":{\"city\":\"Santa Cruz\",\"number\":101,\"contactphone\":\"408-453-8955\",\"state\":\"CA\",\"street\":\"Pacific Ave\",\"zip\":95008},\"email\":\"john.smith@reachmail.com\"}")) RETURNING *

Explanation:

You must provide a string that is a valid JSON text. In this example, the INSERT statement parses the string using the parse_json function to create JSON objects, which are then updated as elements in the details field. Notice that the value for the id field, which is the primary key is auto-generated as you have declared it as an IDENTITY column. For more details, see Inserting Rows with an IDENTITY Column. Also, since you have not provided any values for the books fields in this example, they are populated with NULL values.

Output:

{
  "id" : 2,
  "details" : {
    "DOB" : "22-2-1995",
    "address" : {
      "city" : "Santa Cruz",
      "contactphone" : "408-453-8955",
      "number" : 101,
      "state" : "CA",
      "street" : "Pacific Ave",
      "zip" : 95008
    },
    "email" : "john.smith@reachmail.com",
    "firstName" : "John",
    "lastName" : "Smith"
  },
  "Book1" : null,
  "Book2" : null,
  "Book3" : null
}

Example 2: Fetch from the library application, the book titles of the borrowed books for a user.

In the userslib table above, update the book fields for a user with the details of the books that are borrowed from the library.

UPSERT into userslib values (2, {"DOB":"22-2-1995","address":{"city":"Santa Cruz","contactphone":"408-453-8955","number":101,"state":"CA","street":"Pacific Ave","zip":95008},"email":"john.smith@reachmail.com","firstName":"John","lastName":"Smith"}, '{"doc":{"title":"A Tale of two cities", "author":"Charles Dickens", "site":"brooks.publishers.com"}}', {"doc":'{"title":"Harry Potter", "author":"J K Rowling", "site":"brooks.publishers.com"}'}, {"doc":{"title":"Percy Jackson", "author":"Rick Riodran", "site":"brooks.publishers.com"}}) RETURNING *;

If any JSON data is in a string format inadvertently while updating the book details, the operation still succeeds as the string data is a valid JSON. However, the fields are populated with the unparsed string as follows:

{
  "id" : 2,
  "details" : {
    "DOB" : "22-2-1995",
    "address" : {
      "city" : "Santa Cruz",
      "contactphone" : "408-453-8955",
      "number" : 101,
      "state" : "CA",
      "street" : "Pacific Ave",
      "zip" : 95008
    },
    "email" : "john.smith@reachmail.com",
    "firstName" : "John",
    "lastName" : "Smith"
  },
  "Book1" : "{\"doc\":{\"title\":\"A Tale of two cities\", \"author\":\"Charles Dickens\", \"site\":\"brooks.publishers.com\"}}",
  "Book2" : {
    "doc" : "{\"title\":\"Harry Potter\", \"author\":\"J K Rowling\", \"site\":\"brooks.publishers.com\"}"
  },
  "Book3" : {
    "doc" : {
      "author" : "Rick Riordan",
      "site" : "brooks.publishers.com",
      "title" : "Percy Jackson"
    }
  }
}

Here, the Book1 field is populated as a complete string. The Book2 field has a doc attribute, which is JSON, however, the value is a string. The Book3 field is a proper JSON document.

You can use the parse_json function to select the JSON values from the table as follows:

SELECT 
u.id, u.details.email,
parse_json(u.Book1).doc.title as title1,
parse_json(u.Book2.doc).title as title2,
u.Book3.doc.title as title3
FROM userslib u WHERE id=2;

Explanation:

You can use the parse_json function to parse the string values in the JSON field. In the Book1 field above, the value is a JSON document stored as a string. You parse the complete string to convert it to a JSON object and then select the title field. In the Book2 field, the value of the doc attribute is a JSON document stored as a string. Here, you parse the attribute value to convert it to a JSON object and then select the title field. The Book3 field is a valid JSON object, from which you can extract the title value directly using the path expression.

Output:

{
  "id" : 2,
  "email" : "john.smith@reachmail.com",
  "title1" : "A Tale of two cities",
  "title2" : "Harry Potter",
  "title3" : "Percy Jackson"
}