SQL json_extract returns null

Question

I am attempting to extract from my json object

hits = [{“title”: “Facebook”,
         “domain”: “facebook.com”},
        {“title”: “Linkedin”,
         “domain”: “linkedin.com”}]

When I use: json_extract(hits,'$.title') as title, nothing is returned. I would like the result to be: [Facebook, Linkedin]. However, when I extract by a scalar value, ex.: json_extract_scalar(hits,'$[0].title') as title, it works and Facebook is returned. hits contains a lot of values, so I need to use json_extract in order to get all of them, so I can't do each scalar individually. Any suggestions to fix this would be greatly appreciated.

I get INVALID_FUNCTION_ARGUMENT: Invalid JSON path: '$.title' as an error for $.title (double stars). When I try unnest I get INVALID_FUNCTION_ARGUMENT: Cannot unnest type: varchar as an error and INVALID_FUNCTION_ARGUMENT: Cannot unnest type: json. I get SYNTAX_ERROR: line 26:19: Column '$.title' cannot be resolved when I try double quotes

Do you need mysql or athena query? – Guru Stron Sep 16 '21 at 10:55 — Guru Stron, Sep 16 '21 at 10:55

score 0 · Answer 1 · edited Sep 29 '21 at 14:08

Fits you have an array. So $.title doesn't exist see below

Second, you have not a valid json, is must have double quotes " like the example shows

 SET @a := '[{
        "title": "Facebook",
        "domain": "facebook.com"
    },
    {
 
        "title": "Linkedin",
        "domain": "linkedin.com"
    }
 ]'

 SELECT json_extract(@a,'$[0]') as title

 | title                                           |
 | :---------------------------------------------- |
 | {"title": "Facebook", "domain": "facebook.com"} |

 SELECT JSON_EXTRACT(@a, "$[0].title") AS 'from'

 | from       |
 | :--------- |
 | "Facebook" |

 SELECT @a

 | @a                                                                                                                                                                                                                            |
 | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
 | [{<br>                "title": "Facebook",<br>                "domain": "facebook.com"<br>        },<br>        {<br><br>                "title": "Linkedin",<br>                "domain": "linkedin.com"<br>        }<br>] |

db<>fiddle here

Thanks. But how do I extract all titles? Not just the first one. — Kian O'Connor, Sep 15 '21 at 23:51
Use Instead of 0 * JSON has to be learnt so read up on it or use a normalized table — nbk, Sep 16 '21 at 01:55

score 0 · Answer 2 · answered Sep 16 '21 at 11:06

Correct json path to exract all titles is $.[*].title (or $.*.title), though it is not supported by athena. One option is to cast your json to array of json and use transform on it:

WITH dataset AS (
    SELECT * FROM (VALUES   
       
       (JSON '[{"title": "Facebook",
         "domain": "facebook.com"},
        {"title": "Linkedin",
         "domain": "linkedin.com"}]')
 ) AS t (json_string))


SELECT transform(cast(json_string as ARRAY(JSON)), js -> json_extract_scalar(js, '$.title'))
FROM dataset

Output:

_col0
[Facebook, Linkedin]

SQL json_extract returns null

2 Answers2