CODEWITHSUNDEEP
oracle-databasejson-value
Landon Statis
Landon Statis

Reputation: 849

Oracle - JSON_VALUE returns NULL

I have a JSON string as below, and when I run the following query, it does not return anything. The JSON is valid, so am I missing something?

SELECT JSON_VALUE(json_content, '$.section_1') FROM messages;


{"section_1":{"section_publish":true,"section_body":"<p><b>Overview<\/b><\/p><p>Launched on 19\/06\/1992, the NUVEEN NEW YORK SELECT TAX-FREE INCOME (NXN) is a passively managed exchange traded fund designed to provide a broad exposure to<\/p>"}}

Upvotes: 1

Views: 4605

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6064

As eaolson stated, JSON_VALUE will only return a SCALAR value. If you want to return the JSON string contained in "section_1" you can use JSON_TABLE while using the keywords FORMAT JSON.

WITH
    messages
    AS
        (SELECT '{"section_1":{"section_publish":true,"section_body":"<p><b>Overview<\/b><\/p><p>Launched on 19\/06\/1992, the NUVEEN NEW YORK SELECT TAX-FREE INCOME (NXN) is a passively managed exchange traded fund designed to provide a broad exposure to<\/p>"}}'    AS json_content
           FROM DUAL)
SELECT j.*
  FROM messages
       CROSS JOIN
       JSON_TABLE (
           json_content,
           '$'
           COLUMNS section1 FORMAT JSON PATH '$.section_1',
           section1_publish PATH '$.section_1.section_publish',
           section1_body PATH '$.section_1.section_body') j;

Upvotes: 0

eaolson
eaolson

Reputation: 15090

From the documentation:

The function uses the path expression to evaluate expr and find a scalar JSON value that matches, or satisfies, the path expression.

Your path expression would return a JSON object, not a scalar, I believe. That's an error and the default behavior is to return null. You could try adding an ERROR ON ERROR clause and seeing if that raises an error.

Upvotes: 1

Related Questions