Skip to content

feat: strip_null_value func #17627

@BohuTANG

Description

@BohuTANG

Summary

Introduces a new function STRIP_NULL_VALUE() that converts a JSON null value to a SQL NULL value.

Syntax

STRIP_NULL_VALUE( <variant_expr> )

Example:

-- Create a simple table with JSON data
CREATE TABLE users (user_data VARIANT);

-- Insert two records: one with a null phone number, one with a value
INSERT INTO users 
  SELECT PARSE_JSON('{"name": "Alice", "phone": null}');
  
INSERT INTO users 
  SELECT PARSE_JSON('{"name": "Bob", "phone": "555-1234"}');

-- Compare regular access vs using STRIP_NULL_VALUE
SELECT 
  user_data:name AS name,
  user_data:phone AS original_phone,      -- JSON null appears as 'null'
  STRIP_NULL_VALUE(user_data:phone) AS phone_number  -- Converts JSON null to SQL NULL
FROM users;

-- Result:
-- +----------+----------------+--------------+
-- | NAME     | ORIGINAL_PHONE | PHONE_NUMBER |
-- +----------+----------------+--------------+
-- | "Alice"  | null           | NULL         |
-- | "Bob"    | "555-1234"     | "555-1234"   |
-- +----------+----------------+--------------+

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions