Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


CREATE TABLE t
  (id          INT NOT NULL PRIMARY KEY,
   jsoncolumn VARCHAR2 (256)
   CONSTRAINT ensure_json CHECK (jsoncolumn IS JSON));
INSERT INTO t VALUES
(1, '{
    "key1" : "test",
    "key2" : "test2",
    "key3" : "35",
    "key4" : "3.4",
    "key5" : "35"
}
');
1 rows affected

INSERT INTO t VALUES
(2, '{
    "key1" : "some",
    "key2" : "other",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "8"
}
');
1 rows affected

INSERT INTO t VALUES
(3, '{
    "key1" : "yet",
    "key2" : "another",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "35"
}
');
1 rows affected

INSERT INTO t VALUES
(4, NULL);
1 rows affected

INSERT INTO t VALUES
(5, '{
    "33" : "here",
    "34" : "the",
    "35" : "keys",
    "36" : "are",
    "37" : "numbers"
}
');
1 rows affected

SELECT * FROM t;


ID
JSONCOLUMN


1
{
    "key1" : "test",
    "key2" : "test2",
    "key3" : "35",
    "key4" : "3.4",
    "key5" : "35"
}


2
{
    "key1" : "some",
    "key2" : "other",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "8"
}


3
{
    "key1" : "yet",
    "key2" : "another",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "35"
}


4
null

5
{
    "33" : "here",
    "34" : "the",
    "35" : "keys",
    "36" : "are",
    "37" : "numbers"
}



SELECT * FROM t WHERE t.jsoncolumn LIKE '%35%';


ID
JSONCOLUMN


1
{
    "key1" : "test",
    "key2" : "test2",
    "key3" : "35",
    "key4" : "3.4",
    "key5" : "35"
}


3
{
    "key1" : "yet",
    "key2" : "another",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "35"
}


5
{
    "33" : "here",
    "34" : "the",
    "35" : "keys",
    "36" : "are",
    "37" : "numbers"
}



SELECT * FROM t WHERE INSTR(LOWER(t.jsoncolumn), '35') > 0;


ID
JSONCOLUMN


1
{
    "key1" : "test",
    "key2" : "test2",
    "key3" : "35",
    "key4" : "3.4",
    "key5" : "35"
}


3
{
    "key1" : "yet",
    "key2" : "another",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "35"
}


5
{
    "33" : "here",
    "34" : "the",
    "35" : "keys",
    "36" : "are",
    "37" : "numbers"
}



SELECT id, JSON_QUERY(jsoncolumn, '$.*' WITH WRAPPER) as vals 
  FROM t


ID
VALS


1
["test","test2","35","3.4","35"]

2
["some","other","12","4.1","8"]

3
["yet","another","12","4.1","35"]

4
null

5
["here","the","keys","are","numbers"]


SELECT *
  FROM t
WHERE JSON_QUERY(t.jsoncolumn, '$.*' WITH WRAPPER) LIKE '%35%'


ID
JSONCOLUMN


1
{
    "key1" : "test",
    "key2" : "test2",
    "key3" : "35",
    "key4" : "3.4",
    "key5" : "35"
}


3
{
    "key1" : "yet",
    "key2" : "another",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "35"
}



SELECT * 
  FROM t
 WHERE INSTR(JSON_QUERY(t.jsoncolumn, '$.*' WITH WRAPPER), '35') > 0;


ID
JSONCOLUMN


1
{
    "key1" : "test",
    "key2" : "test2",
    "key3" : "35",
    "key4" : "3.4",
    "key5" : "35"
}


3
{
    "key1" : "yet",
    "key2" : "another",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "35"
}



CREATE INDEX jsonindex   
  ON t(jsoncolumn)   
  INDEXTYPE IS CTXSYS.CONTEXT   
  PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)')
select * from T t where JSON_TEXTCONTAINS(t.jsoncolumn, '$', '35')


ID
JSONCOLUMN


1
{
    "key1" : "test",
    "key2" : "test2",
    "key3" : "35",
    "key4" : "3.4",
    "key5" : "35"
}


3
{
    "key1" : "yet",
    "key2" : "another",
    "key3" : "12",
    "key4" : "4.1",
    "key5" : "35"
}

ID	JSONCOLUMN
1	{ "key1" : "test", "key2" : "test2", "key3" : "35", "key4" : "3.4", "key5" : "35" }
2	{ "key1" : "some", "key2" : "other", "key3" : "12", "key4" : "4.1", "key5" : "8" }
3	{ "key1" : "yet", "key2" : "another", "key3" : "12", "key4" : "4.1", "key5" : "35" }
4	null
5	{ "33" : "here", "34" : "the", "35" : "keys", "36" : "are", "37" : "numbers" }

ID	VALS
1	["test","test2","35","3.4","35"]
2	["some","other","12","4.1","8"]
3	["yet","another","12","4.1","35"]
4	null
5	["here","the","keys","are","numbers"]