Getting Data LLM-Ready with JSON in Oracle Database

 If you are building an AI-powered application, especially with tools and function calling you know that using JSON could greatly improve the accuracy of your application and the LLM would be able to give more grounded response. That is where this blog post is helping to show you how easy and powerful it is to use JSON with Oracle PL/SQL, which is still very much relevant in today's AI world.

Oracle Database provides native support for JavaScript Object Notation (JSON) data, allowing you to store, index, and query JSON data using standard SQL and PL/SQL.

Benefits of Using JSON in Oracle Database

• Schemaless development: Quickly react to changing application requirements without needing to change storage schemas.
• Flexible data analysis and reporting: Leverage the power of SQL and relational databases for complex data analysis and reporting.
• Rock-solid data protection and access control: Ensure data integrity and security with Oracle Database's robust features.

JSON data can be stored, indexed, and queried without defining a schema. Oracle Database supports JSON natively, providing features like transactions, indexing, declarative querying, and views. JSON data is stored using standard SQL data types such as VARCHAR2, CLOB, and BLOB. It is recommended to use an is_json check constraint to ensure column values are valid JSON instances.

PL/SQL supports SQL code, including SQL code that accesses JSON data. You can use SQL/JSON functions and conditions as built-in PL/SQL functions.

Additionally, PL/SQL provides object types for JSON, allowing for fine-grained construction and manipulation of in-memory JSON data.

Let's say we have a JSON object that represents a list of books:

declare

  v_json      clob;

  v_parsed    json_object_t;

  v_books     json_array_t;

  v_book      json_object_t;

  v_title     varchar2(100);

  v_author    varchar2(100);

  v_price     number;

begin

  -- Load JSON Data

  v_json := '{

    "books": [

      {

        "title": "Book 1",

        "author": "Author 1",

        "price": 10.99

      },

      {

        "title": "Book 2",

        "author": "Author 2",

        "price": 9.99

      },

      {

        "title": "Book 3",

        "author": "Author 3",

        "price": 12.99

      }

    ]

  }';

  -- Parse JSON

  v_parsed := json_object_t.parse(v_json);

  v_books := v_parsed.get_array('books');

  -- Loop through books

  for i in 1 .. v_books.get_size

  loop

    v_book := v_books.get_object(i);

    v_title := v_book.get_string('title');

    v_author := v_book.get_string('author');

    v_price := v_book.get_number('price');

    -- Output book details

    dbms_output.put_line(v_title || ' by ' || v_author || ', Price: ' || v_price);

  end loop;

end;

/

The output of this script would be:

Book 1 by Author 1, Price: 10.99

Book 2 by Author 2, Price: 9.99

Book 3 by Author 3, Price: 12.99

By leveraging Oracle Database's native support for JSON data, you can efficiently store, query, and analyze JSON data using standard SQL and PL/SQL.