Commits

Andrew Dunstan  committed 9a5388e

json_get_path_as_text

  • Participants
  • Parent commits 2ef6e8a

Comments (0)

Files changed (5)

File src/backend/utils/adt/jsonfuncs.c

     TupleDesc   ret_tdesc;
 	MemoryContext tmp_cxt;
 	char  *result_start;
+	bool  normalize_results;
+	char  *normalized_scalar;
 } eachState, *EachState;
 
 /* state for json_unnest */
 	if (array_contains_nulls(path))
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-				 errmsg("cannot call json_get_path with null path elements")));
+				 errmsg("cannot call %s with null path elements",
+						"json_get_path_as_text")));
 
 
     deconstruct_array(path, TEXTOID, -1, false, 'i',
 			ereport(
 				ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-				 errmsg("cannot call json_get_path with empty path elements")));
+				 errmsg("cannot call %s with empty path elements",
+						"json_get_path_as_text")));
 	}
 
 	result = get_worker(jsonstr, NULL, -1, pathstr, npath, false);
 		PG_RETURN_NULL();
 }
 
+PG_FUNCTION_INFO_V1(json_get_path_as_text);
+
+Datum
+json_get_path_as_text(PG_FUNCTION_ARGS)
+{
+	text	   *json = PG_GETARG_TEXT_P(0);
+	ArrayType  *path = PG_GETARG_ARRAYTYPE_P(1);
+	char	   *jsonstr = text_to_cstring(json);
+	text	   *result;
+    Datum      *pathtext;
+    bool       *pathnulls;
+    int         npath;
+    char      **pathstr;
+	int         i;
+
+	if (array_contains_nulls(path))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("cannot call %s with null path elements",
+						"json_get_path_as_text")));
+
+
+    deconstruct_array(path, TEXTOID, -1, false, 'i',
+                      &pathtext, &pathnulls, &npath);
+    
+    pathstr = palloc(npath * sizeof(char *));
+
+    for (i = 0; i < npath; i++)
+	{
+		pathstr[i] = TextDatumGetCString(pathtext[i]);
+		if (*pathstr[i] == '\0')
+			ereport(
+				ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("cannot call %s with empty path elements",
+						"json_get_path_as_text")));
+	}
+
+	result = get_worker(jsonstr, NULL, -1, pathstr, npath, true);
+
+	if (result != NULL)
+		PG_RETURN_TEXT_P(result);
+	else
+		PG_RETURN_NULL();
+}
+
 static text *
 get_worker(char *json, 
 		   char *field, 
 	bool        get_last = false;
 
 	if (_state->lex_level == 1 && _state->search_type == JSON_SEARCH_OBJECT &&
-	 _state->result_start != NULL && strcmp(fname, _state->search_term) == 0)
+	 strcmp(fname, _state->search_term) == 0)
 	{
 		get_last = true;
 	}
 			get_last = true;
 	}
 
-	if (get_last)
+	if (get_last && _state->result_start != NULL)
 	{
 		int			len = _state->lex->prev_token_terminator - _state->result_start;
 
 	bool        get_last = false;
 
 	if (_state->lex_level == 1 && _state->search_type == JSON_SEARCH_ARRAY &&
-		_state->array_index == _state->search_index && _state->result_start != NULL)
+		_state->array_index == _state->search_index)
 	{
 		get_last = true;
 	}
 		if (_state->lex_level == _state->npath)
 			get_last = true;
 	}
-	if (get_last)
+	if (get_last && _state->result_start != NULL)
 	{
 		int	len = _state->lex->prev_token_terminator - _state->result_start;
 

File src/include/catalog/pg_proc.h

 DATA(insert OID = 5007 (  json_each PGNSP PGUID 12 1 100 0 0 f f f f t t s 1 0 2249 "114" "{114,25,114}" "{i,o,o}" "{from_json,key,value}" _null_ json_each _null_ _null_ _null_ ));
 DESCR("key value pairs of a json object");
 DATA(insert OID = 5008 (  json_get_path	   PGNSP PGUID 12 1 0 25 0 f f f f t f s 2 0 114 "114 1009" "{114,1009}" "{i,v}" "{from_json,path_elems}" _null_ json_get_path _null_ _null_ _null_ ));
-DESCR("get json with path elements");
+DESCR("get value from json with path elements");
 DATA(insert OID = 5009 (  json_unnest      PGNSP PGUID 12 1 100 0 0 f f f f t t s 1 0 114 "114" "{114,114}" "{i,o}" "{from_json,value}" _null_ json_unnest _null_ _null_ _null_ ));
 DESCR("key value pairs of a json object");
+DATA(insert OID = 5010 (  json_get_path_as_text	   PGNSP PGUID 12 1 0 25 0 f f f f t f s 2 0 25 "114 1009" "{114,1009}" "{i,v}" "{from_json,path_elems}" _null_ json_get_path_as_text _null_ _null_ _null_ ));
+DESCR("get value from json as text with path elements");
 
 
 /* uuid */

File src/include/utils/json.h

 extern Datum json_array_length(PG_FUNCTION_ARGS);
 extern Datum json_each(PG_FUNCTION_ARGS);
 extern Datum json_get_path(PG_FUNCTION_ARGS);
+extern Datum json_get_path_as_text(PG_FUNCTION_ARGS);
 extern Datum json_unnest(PG_FUNCTION_ARGS);
 
 #endif   /* JSON_H */

File src/test/regress/expected/json.out

  f6  | "stringy"
 (5 rows)
 
--- get_path
+-- get_path, get_path_as_text
 select json_get_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6');
  json_get_path 
 ---------------
  1
 (1 row)
 
+select json_get_path_as_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6');
+ json_get_path_as_text 
+-----------------------
+ stringy
+(1 row)
+
+select json_get_path_as_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2');
+ json_get_path_as_text 
+-----------------------
+ {"f3":1}
+(1 row)
+
+select json_get_path_as_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text);
+ json_get_path_as_text 
+-----------------------
+ f3
+(1 row)
+
+select json_get_path_as_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text);
+ json_get_path_as_text 
+-----------------------
+ 1
+(1 row)
+
 --unnest
 select json_unnest('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
       json_unnest      

File src/test/regress/sql/json.sql

 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;
 
--- get_path
+-- get_path, get_path_as_text
 
 select json_get_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6');
 select json_get_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2');
 select json_get_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text);
 select json_get_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text);
+select json_get_path_as_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6');
+select json_get_path_as_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2');
+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);
 
 --unnest