I am using AWS Athena,
I have a subquery like this.
select * from (WITH dataset (json_str) AS (
VALUES (
json '{
"address":{
"streetAddress":"101",
"city":"abc",
"state":"",
"phoneNumbers":[
{
"type":"home",
"number":"11"
},
{
"type":"city",
"number":"4"
}
]
}
}'
)
) -- query
select street_address,
city
from (
select JSON_EXTRACT_SCALAR(json_str, '$.address.streetAddress') as street_address,
JSON_EXTRACT_SCALAR(json_str, '$.address.city') as city,
cast(
JSON_EXTRACT(json_str, '$.address.phoneNumbers') as array(json)
) phones
from dataset
)
where
cardinality(
filter(
phones,
js->json_extract_scalar(js, '$.type') = ? **-- Parameter 1**
and json_extract_scalar(js, '$.number') = '4'
)
) > 0 ) as t where t.city =? **-- Parameter 2**
When in AWS console, it gives me the error: Please find the screenshot
Error Message: SYNTAX_ERROR: line 1:1: Incorrect number of parameters: expected 1 but found 2