Json_get_type inconsistent results

Help JSON
1

This function seems to return inconsistent results. I have some json docs that are also not consistent. Some have an array at inputjson::Maps::Map and others don’t. I’m trying to bring the json into a procedure and process it conditionally based on the result of json_get_type.
I’m using --(A)-- and --(B)-- as labels for this discussion.
Here’s the procedure:
delimiter //
create or replace procedure sp_wafermap (batch query(inputjson JSON))
as
declare
jjj varchar(10);
begin
select json_get_type(inputjson::Maps::Map) into jjj from batch; --(A)–
insert into wafermap (entered, data, filesize)
select
now(),
json_get_type(inputjson::Maps::Map), --(B)–
length(inputjson)
FROM batch;
end //
delimiter ;

–(A)-- returns an error that there are too many records.
When I delete --(A)–, --(B)-- returns the correct answer.

Any ideas why this is happening and a way to work around it?

Thanks,
Tim

2

What is the exact error message and where are you running the query from (what tool?)

Your query A will fail if batch has more than one row in it, by design. SELECT expr INTO var_name does that.

3

Thanks for getting back to me!

I’m using the SIngleStore Studio web client.

This is the error message: Unhandled exception Type: ER_SUBQUERY_NO_1_ROW (1242) Message: Subquery returns more than 1 row Callstack: #0 Line 5 in jtest.sp_wafermap called from #1 Line 1 in helper

This procedure is being called by a pipeline which is here:
create or replace pipeline pl_wafermap as
load data S3 ‘example/wafers/*.json’
CONFIG ‘{“endpoint_url”:“http://xxxxxxx/”}’
CREDENTIALS ‘{“aws_access_key_id”: “xxxxxx”, “aws_secret_access_key”: “xxxxxxx”}’
batch_interval 1
into procedure sp_wafermap
(inputjson ← %)
format json;

Is the pipeline passing a batch of 1000 documents into the procedure?

Thanks

4

ok, got it.

I simply run 2 queries.

delimiter //
create or replace procedure sp_wafermap (batch query(inputjson JSON))
as
begin
insert into wafermap (run, created, jsontype, jsoncol)
select
1, now(),
json_get_type(inputjson::Maps::Map),
inputjson::Maps::Map
FROM batch where json_get_type(inputjson::Maps::Map) = ‘object’;
insert into wafermap (run, created, jsontype, jsoncol)
select
2, now(),
json_get_type(inputjson::Maps::Map),
table_col
FROM batch join table(json_to_array(inputjson::Maps::Map))
where json_get_type(inputjson::Maps::Map) = ‘array’;
end //
delimiter ;

1 Like