Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: March 06, 2019 - 8:10 pm UTC

Last updated: March 06, 2019 - 8:10 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

Hi Beda,

What is the added value of JSON_SERIALIZE(expr) compared to JSON_QUERY(expr, '$')? Are there significant differences in the actual code executed underneath? Thanks in advance, Stew Ashton

and we said...

Hi Stew,

JSON_QUERY with path expression '$' is almost equivalent to JSON_SERIALIZE but will still evaluate the path expression '$' using the path engine (i.e. will be a little bit slower). JSON_QUERY also has some options that JSON_SERIALIZE does not have like EMPTY OBJECT ON ERROR and similar. 

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PRETTY

Laurent Schneider, March 07, 2019 - 8:30 am UTC

If you prefer formatted output, use JSON_SERIALIZE(expr PRETTY)

https://oracle-base.com/articles/19c/mapping-of-json-data-to-and-from-sql-object-types-19c

from 12.2

Rajeshwaran, Jeyabal, March 07, 2019 - 12:39 pm UTC

....
If you prefer formatted output, use JSON_SERIALIZE(expr PRETTY)
....


However since 12.2 JSON_QUERY also support the PRETTY formatting of JSON documents.

demo@ORA12C> select data from json_documents ;

DATA
------------------------------------------------------------
{"id":1,"first_name":"Iron","last_name":"Man"}

demo@ORA12C> select json_query( data, '$' pretty ) from json_documents;

JSON_QUERY(DATA,'$'PRETTY)
-----------------------------------------------------------------------------
{
  "id" : 1,
  "first_name" : "Iron",
  "last_name" : "Man"
}


demo@ORA12C>

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.