string null vs null - Githubissues

Given a JSON file:

{
  "id": "1234",
  "timestamp": "2018-05-31 00:00:00",
  "foo": {
    "key1": null,
    "key2": "value"
  }
}

Using the following table create:

CREATE EXTERNAL TABLE test_table
(
    `id` string,
    `timestamp` timestamp,
    `foo` string
) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("explicit.null"="true");

When querying using the JSON_EXTRACT_SCALAR functions the null is treated as a string "null" rather than null. For example the query below would return no results:

SELECT * FROM test_table WHERE JSON_EXTRACT_SCALAR(foo, '$.foo.key1') IS NULL

rcongiu / Hive-JSON-Serde

string null vs null #208