1

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>"}}
Landon Statis
  • 683
  • 2
  • 10
  • 25

2 Answers2

1

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.

eaolson
  • 14,717
  • 7
  • 43
  • 58
  • What I am looking to finally do, is to retrieve the portion after "section_body". There will eventually be a "section_2" and it will have a "section_body" that I'll want to retrieve. But I would think the query would return something...... – Landon Statis Feb 19 '21 at 23:18
  • I think `$.section_1.section_body` will return that element. – eaolson Feb 19 '21 at 23:39
  • @LandonStatis - The query **does** return something: `null`. eaolson is right - if you want to retrieve a scalar string, namely the value associated with the key "section_body", you **can** do that with `JSON_VALUE`, exactly as eaolson showed you in the comment above this one. –  Feb 20 '21 at 06:28
0

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;
EJ Egyed
  • 5,791
  • 1
  • 8
  • 23
  • That works. Thanks. I tried using JSON_QUERY, like this: SELECT JSON_QUERY(json_content, '$.section_1.section_body' ERROR ON ERROR) AS value FROM message; But it just gives an error: ORA-40441: JSON syntax error. Any thoughts on that? – Landon Statis Feb 19 '21 at 23:38
  • @LandonStatis - Why would you try using `JSON_QUERY()`? That function returns a JSON string, not a scalar value (a string, in your case). When the path given to `JSON_QUERY()` leads to a leaf node, you **must** use the `WITH WRAPPER` clause (hard-coded key words) right before `ERROR ON ERROR`, and the returned string **will** be enclosed in brackets; not quite what you need. Even `JSON_TABLE()` is overkill for what you need (if you really only need to extract **one** value from the whole JSON string); eaolson showed you how to do it with `JSON_VALUE()` in a comment to his answer. –  Feb 20 '21 at 15:23