Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5264d91

Browse files
committed
Add json_array_elements_text function.
This was a notable omission from the json functions added in 9.3 andthere have been numerous complaints about its absence.Laurence Rowe.
1 parent699b1f4 commit5264d91

File tree

7 files changed

+143
-22
lines changed

7 files changed

+143
-22
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10280,6 +10280,27 @@ table2-mapping
1028010280
1
1028110281
true
1028210282
[2,false]
10283+
</programlisting>
10284+
</entry>
10285+
</row>
10286+
<row>
10287+
<entry>
10288+
<indexterm>
10289+
<primary>json_array_elements_text</primary>
10290+
</indexterm>
10291+
<literal>json_array_elements_text(json)</literal>
10292+
</entry>
10293+
<entry><type>SETOF json</type></entry>
10294+
<entry>
10295+
Expands a JSON array to a set of text values.
10296+
</entry>
10297+
<entry><literal>json_array_elements_text('["foo", "bar"]')</literal></entry>
10298+
<entry>
10299+
<programlisting>
10300+
value
10301+
-----------
10302+
foo
10303+
bar
1028310304
</programlisting>
1028410305
</entry>
1028510306
</row>

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

Lines changed: 55 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,9 @@ static void each_object_field_end(void *state, char *fname, bool isnull);
6666
staticvoideach_array_start(void*state);
6767
staticvoideach_scalar(void*state,char*token,JsonTokenTypetokentype);
6868

69+
/* common worker for json_each* functions */
70+
staticinlineDatumelements_worker(PG_FUNCTION_ARGS,boolas_text);
71+
6972
/* semantic action functions for json_array_elements */
7073
staticvoidelements_object_start(void*state);
7174
staticvoidelements_array_element_start(void*state,boolisnull);
@@ -165,6 +168,9 @@ typedef struct ElementsState
165168
TupleDescret_tdesc;
166169
MemoryContexttmp_cxt;
167170
char*result_start;
171+
boolnormalize_results;
172+
boolnext_scalar;
173+
char*normalized_scalar;
168174
}ElementsState;
169175

170176
/* state for get_json_object_as_hash */
@@ -1069,19 +1075,31 @@ each_scalar(void *state, char *token, JsonTokenType tokentype)
10691075
}
10701076

10711077
/*
1072-
* SQLfunction json_array_elements
1078+
* SQLfunctions json_array_elements and json_array_elements_text
10731079
*
10741080
* get the elements from a json array
10751081
*
10761082
* a lot of this processing is similar to the json_each* functions
10771083
*/
10781084
Datum
10791085
json_array_elements(PG_FUNCTION_ARGS)
1086+
{
1087+
returnelements_worker(fcinfo, false);
1088+
}
1089+
1090+
Datum
1091+
json_array_elements_text(PG_FUNCTION_ARGS)
1092+
{
1093+
returnelements_worker(fcinfo, true);
1094+
}
1095+
1096+
staticinlineDatum
1097+
elements_worker(PG_FUNCTION_ARGS,boolas_text)
10801098
{
10811099
text*json=PG_GETARG_TEXT_P(0);
10821100

1083-
/* elementsdoesn't need anyescaped strings, so use false here */
1084-
JsonLexContext*lex=makeJsonLexContext(json,false);
1101+
/* elementsonly needsescaped strings when as_text */
1102+
JsonLexContext*lex=makeJsonLexContext(json,as_text);
10851103
JsonSemAction*sem;
10861104
ReturnSetInfo*rsi;
10871105
MemoryContextold_cxt;
@@ -1124,6 +1142,9 @@ json_array_elements(PG_FUNCTION_ARGS)
11241142
sem->array_element_start=elements_array_element_start;
11251143
sem->array_element_end=elements_array_element_end;
11261144

1145+
state->normalize_results=as_text;
1146+
state->next_scalar= false;
1147+
11271148
state->lex=lex;
11281149
state->tmp_cxt=AllocSetContextCreate(CurrentMemoryContext,
11291150
"json_array_elements temporary cxt",
@@ -1146,7 +1167,17 @@ elements_array_element_start(void *state, bool isnull)
11461167

11471168
/* save a pointer to where the value starts */
11481169
if (_state->lex->lex_level==1)
1149-
_state->result_start=_state->lex->token_start;
1170+
{
1171+
/*
1172+
* next_scalar will be reset in the array_element_end handler, and
1173+
* since we know the value is a scalar there is no danger of it being
1174+
* on while recursing down the tree.
1175+
*/
1176+
if (_state->normalize_results&&_state->lex->token_type==JSON_TOKEN_STRING)
1177+
_state->next_scalar= true;
1178+
else
1179+
_state->result_start=_state->lex->token_start;
1180+
}
11501181
}
11511182

11521183
staticvoid
@@ -1158,7 +1189,7 @@ elements_array_element_end(void *state, bool isnull)
11581189
text*val;
11591190
HeapTupletuple;
11601191
Datumvalues[1];
1161-
staticboolnulls[1]= {false};
1192+
boolnulls[1]= {false};
11621193

11631194
/* skip over nested objects */
11641195
if (_state->lex->lex_level!=1)
@@ -1167,10 +1198,23 @@ elements_array_element_end(void *state, bool isnull)
11671198
/* use the tmp context so we can clean up after each tuple is done */
11681199
old_cxt=MemoryContextSwitchTo(_state->tmp_cxt);
11691200

1170-
len=_state->lex->prev_token_terminator-_state->result_start;
1171-
val=cstring_to_text_with_len(_state->result_start,len);
1201+
if (isnull&&_state->normalize_results)
1202+
{
1203+
nulls[0]= true;
1204+
values[0]= (Datum)NULL;
1205+
}
1206+
elseif (_state->next_scalar)
1207+
{
1208+
values[0]=CStringGetTextDatum(_state->normalized_scalar);
1209+
_state->next_scalar= false;
1210+
}
1211+
else
1212+
{
1213+
len=_state->lex->prev_token_terminator-_state->result_start;
1214+
val=cstring_to_text_with_len(_state->result_start,len);
1215+
values[0]=PointerGetDatum(val);
1216+
}
11721217

1173-
values[0]=PointerGetDatum(val);
11741218

11751219
tuple=heap_form_tuple(_state->ret_tdesc,values,nulls);
11761220

@@ -1204,10 +1248,9 @@ elements_scalar(void *state, char *token, JsonTokenType tokentype)
12041248
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
12051249
errmsg("cannot call json_array_elements on a scalar")));
12061250

1207-
/*
1208-
* json_array_elements always returns json, so there's no need to think
1209-
* about de-escaped values here.
1210-
*/
1251+
/* supply de-escaped value if required */
1252+
if (_state->next_scalar)
1253+
_state->normalized_scalar=token;
12111254
}
12121255

12131256
/*

‎src/include/catalog/pg_proc.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4185,6 +4185,8 @@ DATA(insert OID = 3205 ( json_to_recordset PGNSP PGUID 12 1 100 0 0 f f f f f
41854185
DESCR("get set of records with fields from a json array of objects");
41864186
DATA(insertOID=3968 (json_typeofPGNSPPGUID121000fffftfi1025"114"_null__null__null__null_json_typeof_null__null__null_ ));
41874187
DESCR("get the type of a json value");
4188+
DATA(insertOID=3969 (json_array_elements_textPGNSPPGUID12110000fffftti1025"114""{114,25}""{i,o}""{from_json,value}"_null_json_array_elements_text_null__null__null_ ));
4189+
DESCR("elements of json array");
41884190

41894191
/* uuid */
41904192
DATA(insertOID=2952 (uuid_inPGNSPPGUID121000fffftfi102950"2275"_null__null__null__null_uuid_in_null__null__null_ ));

‎src/include/utils/json.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,7 @@ extern Datum json_array_length(PG_FUNCTION_ARGS);
5858
externDatumjson_each(PG_FUNCTION_ARGS);
5959
externDatumjson_each_text(PG_FUNCTION_ARGS);
6060
externDatumjson_array_elements(PG_FUNCTION_ARGS);
61+
externDatumjson_array_elements_text(PG_FUNCTION_ARGS);
6162
externDatumjson_populate_record(PG_FUNCTION_ARGS);
6263
externDatumjson_populate_recordset(PG_FUNCTION_ARGS);
6364
externDatumjson_to_record(PG_FUNCTION_ARGS);

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

Lines changed: 30 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -801,7 +801,7 @@ select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
801801
(1 row)
802802

803803
-- array_elements
804-
select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
804+
select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
805805
json_array_elements
806806
-----------------------
807807
1
@@ -810,9 +810,10 @@ select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'
810810
null
811811
{"f1":1,"f2":[7,8,9]}
812812
false
813-
(6 rows)
813+
"stringy"
814+
(7 rows)
814815

815-
select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q;
816+
select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
816817
value
817818
-----------------------
818819
1
@@ -821,7 +822,32 @@ select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},
821822
null
822823
{"f1":1,"f2":[7,8,9]}
823824
false
824-
(6 rows)
825+
"stringy"
826+
(7 rows)
827+
828+
select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
829+
json_array_elements_text
830+
--------------------------
831+
1
832+
true
833+
[1,[2,3]]
834+
835+
{"f1":1,"f2":[7,8,9]}
836+
false
837+
stringy
838+
(7 rows)
839+
840+
select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
841+
value
842+
-----------------------
843+
1
844+
true
845+
[1,[2,3]]
846+
847+
{"f1":1,"f2":[7,8,9]}
848+
false
849+
stringy
850+
(7 rows)
825851

826852
-- populate_record
827853
create type jpop as (a text, b int, c timestamp);

‎src/test/regress/expected/json_1.out‎

Lines changed: 30 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -801,7 +801,7 @@ select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
801801
(1 row)
802802

803803
-- array_elements
804-
select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
804+
select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
805805
json_array_elements
806806
-----------------------
807807
1
@@ -810,9 +810,10 @@ select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'
810810
null
811811
{"f1":1,"f2":[7,8,9]}
812812
false
813-
(6 rows)
813+
"stringy"
814+
(7 rows)
814815

815-
select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q;
816+
select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
816817
value
817818
-----------------------
818819
1
@@ -821,7 +822,32 @@ select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},
821822
null
822823
{"f1":1,"f2":[7,8,9]}
823824
false
824-
(6 rows)
825+
"stringy"
826+
(7 rows)
827+
828+
select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
829+
json_array_elements_text
830+
--------------------------
831+
1
832+
true
833+
[1,[2,3]]
834+
835+
{"f1":1,"f2":[7,8,9]}
836+
false
837+
stringy
838+
(7 rows)
839+
840+
select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
841+
value
842+
-----------------------
843+
1
844+
true
845+
[1,[2,3]]
846+
847+
{"f1":1,"f2":[7,8,9]}
848+
false
849+
stringy
850+
(7 rows)
825851

826852
-- populate_record
827853
create type jpop as (a text, b int, c timestamp);

‎src/test/regress/sql/json.sql‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -265,8 +265,10 @@ select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
265265

266266
-- array_elements
267267

268-
select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
269-
select*from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q;
268+
select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
269+
select*from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
270+
select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
271+
select*from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
270272

271273
-- populate_record
272274
createtypejpopas (atext, bint, ctimestamp);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp