26012

Is it good to use SQL function like JSON_EXTRACT for large number of data

Question:

I'm implementing a DB, that is going to hit more than <strong>2000</strong> records per day and I'm going to fetch the data by using <strong>SQL</strong> Function such as <strong>Mysql JSON_EXTRACT</strong> and <strong>ORACLE JSON_VALUE</strong>. becarse. I'm storing data in JSON column.

I will use <strong>Mysql JSON_EXTRACT</strong> and <strong>ORACLE JSON_VALUE</strong> functions for search a data range like below

SELECT * FROM audit where json_extract(detail_x,'$.eventDt') > '2017-10-01 00:00:00' And json_extract(detail,'$.eventDt') < '2018-11-01 00:00:00'

Max date range will be <strong>30days</strong>. so, Max row count will be around 2000 * 30 = <strong>60000</strong>.

My question is. is it fine to use <strong>SQL</strong> Function such as <strong>Mysql JSON_EXTRACT</strong> and <strong>ORACLE JSON_VALUE</strong> for this scenario.

Open to disuss. Thanks.

Answer1:

If you know that all rows have eventDt, then you should extract that into a column. An index on the column will then speed many of your queries. Admittedly, the index may not be useful for such a wide timespan, but it should improve performance when you are retrieving a small number of records.

The purpose of JSON should be to store data that has irregular formats that don't fit into columns. MySQL doesn't index JSON directly.

You can always add an index using a generated column:

alter table audit add column eventDt date generated as (json_extract(detail_x,'$.eventDt')) stored; create index idx_audit_eventDt on audit(eventDt);

Recommend

  • AWS Athena json_extract query from string field returns empty values
  • A JOIN B vs B JOIN A [closed]
  • mongoDb $in with aggregate query
  • MYSQL extract json elements where the json contains
  • Weekly and intraday data aggregate
  • Presto unnest json
  • Oracle - Get data from past year (Dynamic)
  • How to search JSON array in MySQL?
  • Calculating server uptime gives “The network path was not found”
  • Filter_Key by Application
  • Importing a module into access programmatically from a *.cls or similar file
  • fabricator with hstore attribute
  • Send data to server whenever internet connection is available iOS
  • Is it safe to uninstall VS Express after installing VS Ultimate?
  • Months difference between dates [duplicate]
  • Refreshing i18n translated string interpolated values in Aurelia
  • Override Data Tip Circle
  • Error:Connection timed out: connect. If you are behind an HTTP proxy, please configure the proxy set
  • regex question for removal of javascript malware
  • How to trigger a KeyPressed event for unit testing in JAVA
  • How do I create a new row inside an angular ng-repeat?
  • Is it possible to disable certain weekdays in DatePickerDialog?
  • Get or convert Week of year to ISO week
  • getFinancials (quantmod) and tq_get (tidy quant) not working?
  • XSLT foreach repeating nodes to flat
  • How to access culture data in globalize.js V1.0.0
  • Excel's Macro-Recorder usage
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • print() is showing quotation marks in results
  • Google Custom Search with transparent background
  • Insert into database using onclick function
  • What is Eclipse's Declaration View used for?
  • output of program is not same as passed argument
  • Does CUDA 5 support STL or THRUST inside the device code?
  • How to handle AllServersUnavailable Exception
  • Can I make an Android app that runs a web view in Chrome 39?
  • Statically linking a C++ library to a C# process using CLI or any other way
  • VBA Convert delimiter text file to Excel
  • Why winpcap requires both .lib and .dll to run?
  • Reading document lines to the user (python)