Reputation: 849
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
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
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