Full-Text Search in JSON using SQL

Using the JSON function JSON_TEXTCONTAINS in SQL, you can search for JSON data containing specified strings.

SELECT COUNT(*)
FROM wiki_en
WHERE JSON_TEXTCONTAINS(content_json, '$.text', 'Relational Database');

In the case of Oracle Database, you can create a full-text index on a JSON column with a simple syntax like the following.

CREATE SEARCH INDEX wiki_en_search_idx ON wiki_en (content_json);

This full-text index is useful for managing JSON with large text data in an RDBMS. However, since it’s uncertain if the performance and index size are feasible for large-volume data, I have conducted a simple test using English Wikipedia data.

The script and its explanation for creating JSON data from the English Wikipedia and loading it into the database are provided in this article.

Setting Up Oracle Database

I considered using the free version, Oracle Database 23c Free, however, the total user data, including the full-text index, eventually exceeded the 12GB limit this time. Therefore, I used the following Oracle Cloud environment.

Oracle Base Database Service

  - DB Version: 23.4.1.24.06 (23ai)
  - Edition: Standard Edition
  - Shape: VM.Standard.E5.Flex
  - CPU Core Count: 1

Log in with sys and create a general user myuser.

sqlplus sys/<password>@<ip_address>:1521/<service_name> as sysdba

CREATE USER myuser IDENTIFIED BY <password> QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO myuser;

Make sure that you can log in as myuser.

sqlplus myuser/<password>@<ip_address>:1521/<service_name>

Import Wikipedia Data

The wiki_en table dump (approximately 17.5 GB) is available below. It includes English Wikipedia data from May 2024 stored in a JSON column and comprises 17,601,669 entries after removing those with duplicate IDs.

• https://objectstorage.ap-singapore-1.oraclecloud.com/n/axykxyyizbcf/b/download/o/wiki_en.dmp

Create an OS directory.

sudo mkdir /u01/dir01
sudo chown oracle:oinstall /u01/dir01/

Download the dump file.

sudo su - oracle
cd /u01/dir01/
wget https://objectstorage.ap-singapore-1.oraclecloud.com/n/axykxyyizbcf/b/download/o/wiki_en.dmp

Log in as sys and grant directory creation privileges to myuser.

GRANT create any directory, drop any directory TO myuser;

Log in as myuser and create the directory.

CREATE DIRECTORY dir01 AS '/u01/dir01';

Use the impdp command to import the table.

impdp myuser/<password>@<ip_address>:1521/<service_name> \
tables=myuser.wiki directory=dir01 dumpfile=wiki_en.dmp logfile=wiki_en.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MYUSER"."WIKI_EN"                       17.9 GB 17601669 rows

The table contains only one column for storing JSON data.

SELECT * FROM wiki_en FETCH FIRST 3 ROWS ONLY;

CONTENT_JSON
--------------------------------------------------------------------------------
{"id":"263479","revid":"47263348","url":"https://en.wikipedia.org/wiki?curid=263479","title":"Nippon Budokan","text":"The , often shortened to simply Budokan, is an indoor arena in Chiyoda, Tokyo, Japan. It was originally built for the inaugural Olympic judo competition in the 1964 Summer Olympics. While its primary purpose is to host martial arts contests, the arena has gained additional fame as one of the world's most outstanding musical performance venues. The Budokan was a popular venue for Japanese professional wrestling for a time, and it has hosted numerous other sporting events, such as the 1967 Women's Volleyball World Championship. Most recently, the arena hosted the Olympic debut of karate in the 2020 Summer Olympics, as well as the judo competition at both the 2020 Summer Olympics and the 2020 Summer Paralympics.\nA number of famous rock music acts have played at the Budokan. The Beatles were the first rock group to play there, in a series of concerts held between June 30 and July 2, 1966. ABBA ended their and held their final live performance there in March 1980. Numerous acts have recorded live albums at the Budokan, including Blur, TVXQ, Bryan Adams, Bob Dylan, Tom Petty, Eric Clapton, Cheap Trick, Neil Young, Dream Theater, Duran Duran, Kiss, Ozzy Osbourne, Mariah Carey, Judas Priest, Paul McCartney, Asia, Ringo Starr, Journey, Deep Purple, Masayoshi Takanaka, and Michael Schenker Group.\nLocation.\nThe Nippon Budokan is located in Kitanomaru Park in the center of Tokyo, two minutes' walking distance from Kudanshita Subway Station, and near the Imperial Palace and Yasukuni Shrine. The 42 m (140 ft) high octagonal structure holds 14,471 people (arena seats: 2,946, 1st floor seats: 3,199, 2nd floor seats: 7,846, standee: 480). The building is modeled after Yumedono (Hall of Dreams) in Hōryū-ji in Nara.\nVenue history.\nMartial arts.\nAlthough the Budokan also functions as a venue for big musical events, its primary purpose is for Japanese martial arts. The national championships of the different branches of major martial arts (judo, kendo, karate, aikido, etc.) are held annually at the Budokan. The Budokan has also been associated with professional wrestling's big shows, typically from All Japan Pro Wrestling and Pro Wrestling Noah. However, due to declining audiences following the death of Mitsuharu Misawa and the retirement of Kenta Kobashi, professional wrestling has ceased running regular shows in the Budokan. During Wrestle Kingdom 12, New Japan Pro-Wrestling announced that its yearly G1 Climax tournament's finals would be held at the Budokan.\nThe Muhammad Ali vs. Antonio Inoki hybrid rules fight held at the Budokan in 1976 is seen as a forerunner to mixed martial arts. K-1, Shooto, Vale Tudo Japan and Pride Fighting Championships have all held events at the arena.\nMusic.\nThe Beatles were the first rock group to perform at the Budokan in a series of five shows held between June 30 and July 2, 1966. Their appearances were met with opposition from those who felt the appearance of a western pop group would defile the martial arts arena.\nIn July 1973, Japanese television recorded the Santana performance at the Budokan.\nThe Budokan gained worldwide fame when American artists Cheap Trick and Bob Dylan used the arena to record their performances, \"Cheap Trick at Budokan\" (1978) and \"Bob Dylan at Budokan\" (1979). The venue is popular for recording live albums because it has good acoustics, is relatively large and Japanese audiences are known for being highly appreciative when appropriate but quiet during performances. Eric Clapton described the Tokyo audience as \"almost overappreciative\" in interviews promoting \"Just One Night\" (1980), his own live album recorded at the Budokan.\nAmerican crossover thrash band Stormtroopers of Death released a live album titled \"Live at Budokan\" (1992), though the title was in jest and the album was recorded at famed New York City venue The Ritz.\nThe original Beatles concert is heavily bootlegged on audio and video; the first night's concert video was officially released by Apple Records in Japan only as \"Beatles Concert at Budokan 1966\", and excerpts are shown in \"The Beatles Anthology\", while the second \"Anthology\" album included the first show's performances of \"Rock and Roll Music\" and \"She's A Woman\". The venue is one of the stages in \"\" video game. Chatmonchy currently holds the record for the largest crowd at the Budokan.\nThe record for the most Budokan music concerts is held by Eikichi Yazawa, 142 times as of December 19, 2017.\nArtists that have released live recordings from the venue include:\nOther events.\nThe National Memorial Service for War Dead is held with the attendance of the Prime Minister, the Emperor and the Empress annually in Budokan on August 15, the day of Japan's surrender.\nAs well as holding the Live Concert in appreciation of the Popular Anime series Lucky Star: Live in Budokan (Anata No Tame Dakara).\nA concert was held in honor of Studio Ghibli's 25th anniversary at the Budokan, hosted by Joe Hisaishi. It included repertoire from most of the films Hisaishi composed for Hayao Miyazaki's Studio Ghibli filmography.\nDiana Ross performed and taped her \"Here and Now\" television special in 1991 to a sold-out audience.\nThe Japan Record Awards took place in the arena from 1985 to 1993 where all of the artists from around the country receive these awards.\nMuhammad Ali won a unanimous decision over Mac Foster in their 1972 heavyweight boxing match.\nOn February 13, 1975, a religious gathering was held to hear Rev. Sun Myung Moon speak.\nOn August 27, 2011, Japan's three biggest professional wrestling promotions; All Japan Pro Wrestling, New Japan Pro-Wrestling and Pro Wrestling Noah came together to produce a charity event titled All Together at the arena. On August 10, 11 and 12, 2018 New Japan Pro-Wrestling held the final 3 days of the G1 Climax in the Budokan, which marked the first time in 15 years that New Japan has promoted an event there. New Japan once again returned to the arena for the final 3 days of the 2019 G1 Climax. New Japan also held the Best of the Super Jr. and World Tag League finals in December 2021, as well as their 49th Anniversary Show and the final two days of the upcoming G1 Climax in 2021.\nJoshi wrestling promotion World Wonder Ring Stardom held their All Star Dream Cinderella event on March 3, 2021. Making this the first time a joshi company to held an event in the venue in 24 years.\nProfessional wrestler and legend in Japan Kenta Kobashi wrestled his final match in Budokan on May 11, 2013, at an event titled Final Burning in Budokan. Kobashi is synonymous with the arena along with fellow wrestlers Toshiaki Kawada and the late Mitsuharu Misawa.\nIn November, the Budokan is a venue for the annual Japan Self-Defense Forces Marching Festival, a yearly tradition and the nation's military tattoo first held here in the fall of 1963. Aside from JSDF bands, foreign armed forces military bands are also invited to join the event.\nThe state funeral of Shinzo Abe was held at the Budokan on 27 September 2022.\nOther uses.\nThe Nippon Budokan is the primary setting of the 1989 fighting game \"\". Players train in various Japanese martial arts, and must then face off at the Budokan arena against computer-controlled opponents.\nA fictional concert hall based on the Nippon Budokan appeared in the music video game ' (2007) under the name \"Kaiju Megadome\". The Beatles' appearance at Nippon Budokan was featured in ' (2009).\nAnother fictional hall based on the Nippon Budokan appeared in the Japanese pro-wrestling video game \"\" (2000)."}
{"id":"263480","revid":"42861628","url":"https://en.wikipedia.org/wiki?curid=263480","title":"Deviation","text":"Deviation may refer to:"}
{"id":"263481","revid":"35936988","url":"https://en.wikipedia.org/wiki?curid=263481","title":"Michael Noonan (Fine Gael politician)","text":"Michael Noonan (born 21 May 1943) is an Irish former Fine Gael politician who served as Minister for Finance from 2011 to 2017, Leader of the Opposition and Leader of Fine Gael from 2001 to 2002, Minister for Health from 1994 to 1997, Minister for Industry and Commerce from 1986 to 1987, Minister for Energy from January 1987 to March 1987 and Minister for Justice from 1982 to 1986. He served as a Teachta Dála (TD) from 1981 to 2020.\nNoonan had been a member of every Fine Gael cabinet since 1982, serving in the cabinets of Garret FitzGerald, John Bruton and Enda Kenny. During these terms of office he held the positions of Justice, Energy, Industry and Commerce, Health and Finance. When Fine Gael lost power after the 1997 general election, Noonan remained an important figure in the party when he became Opposition Spokesperson for Finance.\nHe succeeded John Bruton as Leader of Fine Gael and Leader of the Opposition in 2001, however, he resigned following Fine Gael's disastrous showing at the 2002 general election. After eight years as a backbencher, during which time he served as Chairman of the Public Accounts Committee, Fine Gael leader Enda Kenny appointed Noonan to his front bench in 2010, to his former portfolio of Spokesperson for Finance.\nEarly life.\nThe son of a local school teacher, Noonan was born in Limerick in 1943, but raised in Loughill, County Limerick. He was educated at the local National School and St. Patrick's Secondary School in Glin, before studying to be a primary school teacher at St. Patrick's College, Drumcondra, Dublin. He subsequently completed a BA and H.Dip. in English and Economics at University College Dublin. He began to work as a secondary school teacher in Dublin. Noonan developed an interest in politics from his mother, whose family had been heavily involved in Fine Gael at local level in Limerick, and joined the Dublin branch of the party after graduating from university. He returned to Limerick in the late 1960s, where he took up a teaching post at Crescent College. Here he continued his involvement in politics, canvassing for the Fine Gael candidate, James O'Higgins, in the Limerick East by-election in 1968, caused by the death of Donogh O'Malley.\nPolitical career.\nEarly years: 1974–1982.\nHaving been involved in the local Fine Gael organisation in Limerick since the late 1960s, Noonan first held political office in 1974, when he was elected as a member of Limerick County Council. Having built up a local profile he contested the 1981 general election for the party and secured a seat in Limerick East. Upon taking his Dáil seat, Noonan became a full-time politician, giving up his teaching post and resigning his seat on Limerick County Council. Though Fine Gael formed a coalition government with the Labour Party, Noonan, as a first time TD, remained on the backbenches.\nCabinet minister: 1982–1987.\nFine Gael lost power following the first general election in early 1982, however, Noonan subsequently joined the party's new front bench as Spokesperson for Education. A second general election in late 1982 following the collapse of the Fianna Fáil government, saw another Fine Gael-Labour Party coalition come to power. Just eighteen months after entering the Dáil, Noonan was appointed to the highly sensitive position of Minister for Justice. After a few weeks in office he revealed the illegal phone-tapping of journalists' phones, carried out by the previous Fianna Fáil administration that preceded it in power. That government had authorised illegal phone tapping of the journalists Geraldine Kennedy, Bruce Arnold and Vincent Browne. Seán Doherty signed warrants for the taps while serving as Minister for Justice. Noonan introduced a new Criminal Justice Bill while also bringing in reforms in the Garda Síochána, the courts and the prison service and the facing down of a difficult prison officers' dispute. He also dealt with the wording of the controversial abortion referendum in 1983. Noonan, however, also presided over the justice ministry when inmates in an overcrowded and understaffed Spike Island prison set fire to the building.\nA cabinet reshuffle in 1986, saw Noonan demoted to the position of Minister for Industry and Commerce. Following the withdrawal of the Labour Party from government in 1987, Noonan also briefly took office as Minister for Energy.\nOpposition: 1987–1994.\nFine Gael lost power following the 1987 general election and were confined to the opposition benches. The new Fine Gael leader, Alan Dukes, appointed Noonan to the senior position of front bench Spokesperson for Finance and the Public Service. The party did poorly under Dukes and he was replaced by John Bruton in 1990. Noonan was retained on the new front bench, however, he was demoted to the position of Spokesperson for Transport, Energy and Communications. In 1991, he returned to local politics as a member of Limerick County Council, serving again until 1994. Noonan's period in opposition often saw him at odds with his party leader. Not long after his demotion as Spokesperson for Finance, he announced that he would be taking \"positions of leadership\" on a wide range of important issues. This was seen as a veiled threat to John Bruton's leadership, with Noonan positioning himself as an alternative party leader. In 1994, a number of Fine Gael TDs attempted to oust Bruton as party leader following poor showings in opinion polls. Noonan aligned himself with the rebels and stated that he would stand for the leadership should Bruton be defeated. The latter survived as leader and Noonan resigned from the front bench.\nMinister for Health: 1994–1997.\nIn 1994, the 'Rainbow Coalition' was formed and Noonan became Minister for Health. The Department of Health was embroiled in a scandal at the time regarding blood products contaminated with Hepatitis C virus, caused by the negligence of the Blood Transfusion Service Board. Noonan consistently held an authoritarian line on the case of Brigid McCole and would not budge on his views, suffering as a result of the scandal. He threatened to take Bridget's mother Ellen to the Supreme Court, when she wondered why her daughter had contracted the disease. Noonan was forced to establish the Hepatitis C Tribunal of Inquiry and to issue several apologies for his handling of the affair. Noonan remained as Minister for Health until the 1997 general election. \"The Irish Times\" said \"the woman involved had been infected by a negligent State agency, in the biggest health scandal since its foundation.\" When RTÉ broadcast a drama, \"No Tears\", on Noonan's treatment of Bridget McCole, Justine McCarthy wrote in the \"Irish Independent\" that Noonan \"compounded the perception of a heartless pedant by whingeing about the way he was depicted in the drama's final episode, broadcast on the same night that he declined to appear on \"Questions & Answers\" and when it was reported that yet another woman who was infected by the State had died from the illness.\"\nHis home was picketed by anti-abortion campaign group Youth Defence.\nReturn to Opposition: 1997–2011.\nLeader of Fine Gael: 2001–2002.\nDespite increasing their seats to 54, Fine Gael returned to opposition and Noonan became Opposition Spokesman for Finance following the 1997 general election. In 2001, following a series of disastrous opinion polls, Noonan and his colleague, Jim Mitchell, tabled a motion of no confidence in the leader, John Bruton. The motion was successful in ousting Bruton as leader, with Noonan becoming leader of Fine Gael and Leader of the Opposition with Mitchell becoming deputy leader. Noonan avoided requests to be interviewed on TV and radio programmes, including some on RTÉ and Today FM, ahead of the leadership election.\nAt the 2002 general election, Fine Gael had a disastrous result, dropping from 54 seats to 31 and a number of high-profile front bench member losses, including Alan Dukes, Deirdre Clune, Alan Shatter and deputy leader Jim Mitchell. Noonan resigned as Fine Gael leader on the night of the election.\nTD: 2002–2011.\nHe was replaced by Enda Kenny, the runner-up to Noonan in the 2001 leadership election. However, Noonan remained as a TD, and was re-elected at the 2007 general election, and went on to serve on Kenny's Front bench. He was vice-chairperson of the Joint Oireachtas Committee on Constitutional Amendment and Children.\nMinister for Finance: 2011–2017.\nIn July 2010, Noonan was promoted to the Fine Gael Front Bench as Spokesperson for Finance. In an August 2010 interview with the Sunday Independent, Noonan said he hoped to become Minister for Finance. \nAt the 2011 general election, he was re-elected as a TD for Limerick City, receiving 13,291 (30.8% 1st preference) votes. On 9 March 2011, he was appointed Minister for Finance by Taoiseach Enda Kenny.\nIn March 2011, the International Monetary Fund (IMF) and the European Union (EU) backed the proposed programme for government and gave the coalition the green light to make changes to the terms of the multibillion-euro bailout. After meeting officials from the IMF – including Ireland mission head Ajai Chopra – European Central Bank (ECB) and European Commission (EC), Noonan said it was agreed the terms of the rescue deal would be altered, as long as the financial targets remain the same. \"What was being discussed in general terms was our proposal that the conditions and the memorandum of understanding would be changed for alternative conditions which are in the programme for government,\" Noonan said.\nIn July 2011, Noonan speaking after an EU summit on 21 July said that the new deal agreed with euro zone leaders means a second bailout for Ireland is \"off the table\". The deal will see a reduction in the interest rate on bailout loans to Greece, Ireland, Portugal and Italy. This followed a 10-hour emergency summit at which eurozone leaders agreed to provide a second international bailout worth €109 billion to Greece. The plan will be funded by eurozone countries, the proceeds of privatisations and the anticipated €12.6 billion benefit of a debt buyback programme. Noonan said a provision in the agreement meant Ireland would not have to go back to markets when the programme ended if the country had not reached its deficit target. \"There's a commitment that if countries continue to fulfil the conditions of their programme, the European authorities will continue to supply them with money even when the programme concludes,\" he said. There would be \"little or no easing\" of budgetary conditions for this year, but there could be more positive implications in later years. \"I'm afraid we still have to face the music in December,\" he said.\nIn November 2011, he said the payment of more than €700 million to Anglo Irish Bank bondholders was \"the lesser of two evils\". On 16 May 2012, Noonan caused controversy with his Greek \"holidays\" and \"feta cheese\" comment at a breakfast briefing with Bloomberg news agency. Noonan said these were the only links between Ireland and Greece. He attended the 2012 Bilderberg Conference in his capacity of Minister for Finance at Chantilly, Virginia, from 31 May-3 June 2012. On 5 December 2012, he delivered his second budget as Minister for Finance, which included a new property tax to be introduced in 2013.\nIn February 2013, a deal was reached with the European Central Bank (ECB) in relation to the promissory note used to bail out the former Anglo Irish Bank. Noonan said that the government had achieved its objectives in the negotiations with the ECB, and that the arrangement meant that there would be €1bn less taken from them in terms of taxes and spending cuts up to 2015. Noonan said that the government did not ask for a write down on the Anglo Irish debt during negotiations with the ECB as \"the ECB does not do write downs\".\nOn 15 October 2013, he delivered the budget for 2014.\nFollowing improvements in Ireland's unemployment rate and outlook for growth, the securing of the February 2013 Anglo Irish Bank promissory note deal with the European Central Bank, and Ireland's exit from the EU/IMF/ECB bailout programme and successful return to the bond markets, Noonan was named Europe's best Finance Minister for the previous year in January 2014 by the Financial Times-owned magazine The Banker.\nWhen the European Central Bank raised the limit on the amount of emergency liquidity assistance (ELA) available to Greek banks by about €2 billion at the height of the country's government-debt crisis in June 2015, Noonan joined his German counterpart Wolfgang Schäuble in arguing forcefully for limits on the amount of ELA approved by the central bank unless capital controls were introduced.\nIn July 2016, the Central Statistics Office announced 2015 Irish GDP growth of 26.3% and Irish GNP growth of 18.7%. The growth became known internationally by the pejorative term, leprechaun economics. Noonan attributed the figures to multinational restructuring following the closure of the double Irish tax scheme, however they were subsequently attributed to Apple in 2018 (widely suspected in 2016). Noonan came to the defense of Apple when the European Commission announced in August 2016 that it had found against Apple in its investigation of illegal State aid. Noonan led the rejection of any claim by Ireland to the EU Commission's €13 billion fine on Apple, calling it an \"attack\" by the commission, and was supported by the main opposition party. In October 2016, Noonan introduced changes in the 2016 Finance Act to curb tax abuses of section 110 special purpose vehicles, (securitisation vehicles for IFSC firms), by US distressed debt funds in Ireland (pejoratively called vulture funds in the Irish media). Investigations into these abuses by the financial media, showed the scale and rapid gains these funds were making from NAMA's disposal program, and that these gains were free of Irish taxes. It led to some revision as to whether Noonan had been too quick in selling State assets to distressed debt funds, and had given overly generous tax benefits and incentives.\nRetirement.\nIn May 2017, he announced he would be stepping down as Minister for Finance in the coming weeks when a new Taoiseach was appointed, and as a member of the Dáil at the next general election.\nPersonal life.\nNoonan married Florence Knightley, a native of Castlemaine, County Kerry and a primary school teacher, in 1969. They had three sons: Tim, John and Michael, and two daughters: Orla and Deirdre. In May 2010, Noonan appeared on RTÉ's \"The Frontline\" to talk about his wife's battle with Alzheimer's disease. Florence Noonan died on 23 February 2012 of pneumonia."}

Create the Full-Text Index

Since the temporary tablespace is necessary to create large-size indexes, add a data file to ensure the temporary tablespace can be sufficiently expanded.

Log in as sys and add a few data files.

ALTER TABLESPACE temp ADD TEMPFILE '+DATA';
ALTER TABLESPACE temp ADD TEMPFILE '+DATA';
ALTER TABLESPACE temp ADD TEMPFILE '+DATA';

Log in as myuser and create the full-text index. This process took about 35 minutes in this environment.

CREATE SEARCH INDEX wiki_en_search_idx ON wiki_en (content_json);

INDEX WIKI_EN_SEARCH_IDX created.
Elapsed: 00:46:08.736

Check the segment size.

SELECT
  segment_name
, segment_type
, bytes/1024/1024 AS mb
FROM user_segments
WHERE segment_name LIKE '%WIKI_EN%'
ORDER BY mb DESC;

SEGMENT_NAME                   SEGMENT_TYPE             MB
------------------------------ --------------- -----------
WIKI_EN                        TABLE            16364.0000
DR$WIKI_EN_SEARCH_IDX$I        TABLE            16161.0000
DR$WIKI_EN_SEARCH_IDX$X        INDEX             3335.0000
DR$WIKI_EN_SEARCH_IDX$SN       TABLE             2164.0000
DR$WIKI_EN_SEARCH_IDX$SNI      INDEX             1536.0000
DR$WIKI_EN_SEARCH_IDX$KD       INDEX              544.0000
DR$WIKI_EN_SEARCH_IDX$KR       INDEX              544.0000
DR$WIKI_EN_SEARCH_IDX$K        TABLE              423.0000
DR$WIKI_EN_SEARCH_IDX$B        TABLE                 .0625
DR$WIKI_EN_SEARCH_IDX$C        TABLE                 .0625
DR$WIKI_EN_SEARCH_IDX$G        TABLE                 .0625
DR$WIKI_EN_SEARCH_IDX$H        INDEX                 .0625
DR$WIKI_EN_SEARCH_IDX$N        TABLE                 .0625
DR$WIKI_EN_SEARCH_IDX$NI       INDEX                 .0625
DR$WIKI_EN_SEARCH_IDX$Q        TABLE                 .0625
DR$WIKI_EN_SEARCH_IDX$ST       TABLE                 .0625
DR$WIKI_EN_SEARCH_IDX$STI      INDEX                 .0625
DR$WIKI_EN_SEARCH_IDX$U        TABLE                 .0625
DR$WIKI_EN_SEARCH_IDX$UI       INDEX                 .0625

The tables and indexes with the prefix DR$WIKI_EN are internally created. The full-text index consumes about 1.5 times the data capacity compared to the original table (24 GB against 16 GB).

Full-Text Search in JSON

Using the JSON_TEXTCONTAINS function, you can count the number of Wikipedia pages containing the word “Relational Database” in the text, and it executes in less than 0.1 seconds.

SELECT COUNT(*)
FROM wiki_en
WHERE JSON_TEXTCONTAINS(content_json, '$.text', 'Relational Database');

  COUNT(*)
----------
       757

Elapsed: 00:00:00.047

If you add a hint to avoid using the index, the execution time increases to over 2 minutes.

SELECT /*+ NO_INDEX(wiki_en wiki_en_search_idx) */ COUNT(*)
FROM wiki_en
WHERE JSON_TEXTCONTAINS(content_json, '$.text', 'Relational Database');

  COUNT(*)
----------
       757

Elapsed: 00:02:34.644

To output the content of the JSON values, use the JSON_VALUE function. Long strings contained in the text element should be cast to CLOB.

SELECT JSON_VALUE(content_json, '$.id') AS id
     , JSON_VALUE(content_json, '$.title') AS title
     , JSON_VALUE(content_json, '$.text' RETURNING CLOB) AS text
FROM wiki_en
WHERE JSON_TEXTCONTAINS(content_json, '$.text', 'Relational Database')
FETCH FIRST 3 ROWS ONLY;

ID     TITLE                       TEXT
------ --------------------------- ----------------------------------------------------------
266699 FileMaker                   FileMaker is a cross-platform relational database application developed by 
271832 ABAP                        ABAP (Advanced Business Application Programming, originally "Allgemeiner Be
275651 Shared-nothing architecture A shared-nothing architecture (SN) is a distributed computing architecture 

As shown above, we confirmed that the full-text index is effective for more than 2 million JSON data entries. Although the index size is substantial, this capability is useful for searching JSON content together with table data.