Commits

Andrew Dunstan committed 5cae00c

Fix as_text treatment of null - return an SQL null if we find a JSON null.

Bug found by David Wheeler.

Comments (0)

Files changed (3)

src/backend/utils/adt/jsonfuncs.c

 		 */
 		int			len = _state->lex->prev_token_terminator - _state->result_start;
 
-		_state->tresult = cstring_to_text_with_len(_state->result_start, len);
+		if (isnull  && _state->normalize_results)
+			_state->tresult = (text *) NULL;
+		else
+			_state->tresult = cstring_to_text_with_len(_state->result_start, len);
 	}
 
 	/*
 	}
 	if (get_last && _state->result_start != NULL)
 	{
-		int			len = _state->lex->prev_token_terminator - _state->result_start;
+		int	 len = _state->lex->prev_token_terminator - _state->result_start;
 
-		_state->tresult = cstring_to_text_with_len(_state->result_start, len);
+		if (isnull  && _state->normalize_results)
+			_state->tresult = (text *) NULL;
+		else
+			_state->tresult = cstring_to_text_with_len(_state->result_start, len);
 	}
 }
 
 	text	   *val;
 	HeapTuple	tuple;
 	Datum		values[2];
-	static bool nulls[2] = {false, false};
+	bool        nulls[2] = {false, false};
 
 	/* skip over nested objects */
 	if (_state->lex->lex_level != 1)
 
 	values[0] = CStringGetTextDatum(fname);
 
-	if (_state->next_scalar)
+	if (isnull && _state->normalize_results)
+	{
+		nulls[1] = true;
+		values[1] = NULL;
+	}
+	else if (_state->next_scalar)
 	{
 		values[1] = CStringGetTextDatum(_state->normalized_scalar);
 		_state->next_scalar = false;

src/test/regress/expected/json.out

 );
 INSERT INTO test_json VALUES
 ('scalar','"a scalar"'),
-('array','["zero", "one","two","three","four","five"]'),
-('object','{"field1":"val1","field2":"val2"}');
+('array','["zero", "one","two",null,"four","five"]'),
+('object','{"field1":"val1","field2":"val2","field3":null}');
 SELECT json_get(test_json,'x') 
 FROM test_json
 WHERE json_type = 'scalar';
 ------------------
  field1
  field2
-(2 rows)
+ field3
+(3 rows)
+
+-- nulls
+select (test_json->'field3') is null as expect_false
+from test_json
+where json_type = 'object';
+ expect_false 
+--------------
+ f
+(1 row)
+
+select (test_json->>'field3') is null as expect_true
+from test_json
+where json_type = 'object';
+ expect_true 
+-------------
+ t
+(1 row)
+
+select (test_json->3) is null as expect_false
+from test_json
+where json_type = 'array';
+ expect_false 
+--------------
+ f
+(1 row)
+
+select (test_json->>3) is null as expect_true
+from test_json
+where json_type = 'array';
+ expect_true 
+-------------
+ t
+(1 row)
 
 -- array length
 SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
  f6  | "stringy"
 (5 rows)
 
-select json_each_as_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
+select json_each_as_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}');
  json_each_as_text 
 -------------------
  (f1,"[1,2,3]")
  (f2,"{""f3"":1}")
- (f4,null)
-(3 rows)
+ (f4,)
+ (f5,null)
+(4 rows)
 
 select * from json_each_as_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
  key |  value   
 -----+----------
  f1  | [1,2,3]
  f2  | {"f3":1}
- f4  | null
+ f4  | 
  f5  | 99
  f6  | stringy
 (5 rows)
  1
 (1 row)
 
+-- get_path nulls
+select json_get_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_false;
+ expect_false 
+--------------
+ f
+(1 row)
+
+select json_get_path_as_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_true;
+ expect_true 
+-------------
+ t
+(1 row)
+
+select json_get_path('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_false;
+ expect_false 
+--------------
+ f
+(1 row)
+
+select json_get_path_as_text('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_true;
+ expect_true 
+-------------
+ t
+(1 row)
+
 -- get_path operators
 select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json->array['f4','f6'];
  ?column?  

src/test/regress/sql/json.sql

 
 INSERT INTO test_json VALUES
 ('scalar','"a scalar"'),
-('array','["zero", "one","two","three","four","five"]'),
-('object','{"field1":"val1","field2":"val2"}');
+('array','["zero", "one","two",null,"four","five"]'),
+('object','{"field1":"val1","field2":"val2","field3":null}');
 
 SELECT json_get(test_json,'x') 
 FROM test_json
 FROM test_json
 WHERE json_type = 'object';
 
+-- nulls
+
+select (test_json->'field3') is null as expect_false
+from test_json
+where json_type = 'object';
+
+select (test_json->>'field3') is null as expect_true
+from test_json
+where json_type = 'object';
+
+select (test_json->3) is null as expect_false
+from test_json
+where json_type = 'array';
+
+select (test_json->>3) is null as expect_true
+from test_json
+where json_type = 'array';
+
+
 -- array length
 
 SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
 select json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
 select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
 
-select json_each_as_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
+select json_each_as_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}');
 select * from json_each_as_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
 
 -- get_path, get_path_as_text
 select json_get_path_as_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text);
 select json_get_path_as_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text);
 
+-- get_path nulls
+
+select json_get_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_false;
+select json_get_path_as_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_true;
+select json_get_path('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_false;
+select json_get_path_as_text('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_true;
+
 -- get_path operators
 
 select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json->array['f4','f6'];