Oracle with JSON
Thiago Azadinho - MBA/OCP/OCE/MCSE
DBA / DBRE / DATABASE ADMINISTRATOR / DEVOPS
In this article I want to talk a little about a new feature in Oracle 12c, which is the integration with JSON. This is a very interesting new feature since JSON has been gaining more and more strength in applications for some time now (mainly because of the NoSql databases).
In this article we will talk about it in a very superficial way, more so that we can understand how it works, and in the next articles we will explore this topic in more depth.
To begin, let's create a table that contains a column to receive your values in JSON format. In this case, you can create a common VARCHAR2 column and then place a constraint on this table stating that only records in JSON format can be inserted in this column.
We will use a table that stores course information for example:
CREATE TABLE cursos_json ( cod_curso NUMBER NOT NULL, informacoes VARCHAR2(4000) CONSTRAINT ensure_json CHECK ( informacoes IS JSON ) );
Note that the “information” column is VARCHAR2(4000) and then we create a CHECK type constraint that uses a new “IS JSON” function that basically checks whether certain content is in JSON format or not.
Recommended by LinkedIn
Once this is done, you can now consult a new view that contains the metadata of the tables that use JSON, see:
SELECT * FROM USER_JSON_COLUMNS;
Now as part of our exercise we are going to insert some records, note that it is very simple, you just need to put information in JSON format in your insert. INSERT INTO cursos_json (cod_curso, informacoes) values (1, '{"Curso": "DBA-Essential", "Nível": "Iniciante", "Topicos": ["Instalacao","Configuracao","Manutencao", "Backup / Recover"] }');
INSERT INTO cursos_json (cod_curso, informacoes) values (2, '{“Course”: “Tuning DB / App”, “Level”: “Intermediate”, “Topics”: [“SQL Tuning”,”Instance Tuning”, “OS Tuning”] }');
After the rows have been inserted, we can now consult them, run a common select on your table and see all the records successfully inserted.