json_array_length.sql

-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.

statement
CREATE TABLE test_json_array_length(j string) USING parquet

statement
INSERT INTO test_json_array_length VALUES
  ('[1,2,3,4]'),
  ('[]'),
  ('[1]'),
  (NULL),
  ('[1,2,3,{"f1":1,"f2":[5,6]},4]'),
  ('[[1,2],[3,4],[5,6]]'),
  ('[{"a":1},{"b":2},{"c":3}]'),
  ('[1,2'),
  ('[1,2,3,]'),
  ('not a json'),
  ('{"object": "not array"}'),
  (''),
  ('   '),
  ('[true, false, null]'),
  ('["string1", "string2", "string3"]'),
  ('[1, "mixed", true, null, {"key":"value"}]'),
  ('[1,2,3,4,5,6,7,8,9,10]'),
  ('["line1\nline2", "tab\tseparated", "quote\"here"]'),
  ('{"outer": [1,2,3], "inner": [[1,2],[3,4]]}'),
  ('{"arrays": {"first": [1,2], "second": [3,4,5]}}'),
  ('[{"arr": [1,2,3]}, {"arr": [4,5]}]')

query spark_answer_only
SELECT json_array_length(j) FROM test_json_array_length

query spark_answer_only
SELECT json_array_length('[1,2,3,4]')

query spark_answer_only
SELECT json_array_length('not an array')

query spark_answer_only
SELECT json_array_length('{"key":"value"}')

query spark_answer_only
SELECT json_array_length(NULL)

query spark_answer_only
SELECT json_array_length('[]')

query expect_fallback(Spark's lenient JSON parser allows single quotes, unescaped controls, and trailing content, while Comet's serde_json requires strict JSON.)
SELECT json_array_length("[{'key':'value'}]")