Extract Json in Presto

0
votes

I have got below data in some complex format of json, which is neither a pure json not array.

enter image description here

I have tried to pull the specific values by using below queries that did not work

SELECT json_array_extract_scalar(bankdetails, '$.bank') FROM ade.evangelist_oao.oaocustomfundingupdateaoapplicationsvc ;

Query failed (#20200717_121411_01261_cav8k): line 1:8: Unexpected parameters (row(bank varchar), varchar(6)) for function json_extract_scalar. Expected: json_extract_scalar(varchar(x), JsonPath) , json_extract_scalar(json, JsonPath) [DB Errorcode=1]

SELECT json_extract_scalar(accounts, '$.fundingsrcs.method') FROM ade.evangelist_oao.oaocustomfundingupdateaoapplicationsvc ;

Query failed (#20200717_121600_01270_cav8k): line 1:8: Unexpected parameters (array(row(fundingsrcs array(row(method varchar,amount bigint)))), varchar(20)) for function json_extract_scalar. Expected: json_extract_scalar(varchar(x), JsonPath) , json_extract_scalar(json, JsonPath) [DB Errorcode=1]

How can i get the b840 value of bankdetails field and CUSTOMFUNDING value of accounts field in this case?

1
jsonextractpresto

1 Answers

3
votes

The actual type is ROW (not JSON), so you can retrieve the value with

SELECT bankdetails.bank FROM ...