Following the discussion on pgsql-general, I thought I'd have a go
implementing json_array_elements_text following the same pattern as
json_each_text. The function makes it possible to join elements of a
json array onto a table, for example:
CREATE TABLE object (name TEXT PRIMARY KEY, properties JSON);
INSERT INTO object (name, properties) VALUES
('one', '{}'),
('two', '{"links": ["one"]}'),
('three', '{"links": ["one", "two"]}');
SELECT source.name, target.name
FROM (
SELECT *, json_array_elements_text(properties->'links')::text AS
link_to FROM object
) AS source
JOIN object target ON source.link_to = target.name;
My particular use case has uuid keys for object, which are difficult
to cast from json.
Laurence
---
doc/src/sgml/func.sgml | 22 ++++++++++++
src/backend/utils/adt/jsonfuncs.c | 67 +++++++++++++++++++++++++++++-------
src/include/catalog/pg_proc.h | 2 ++
src/include/utils/json.h | 1 +
src/test/regress/expected/json.out | 34 +++++++++++++++---
src/test/regress/expected/json_1.out | 34 +++++++++++++++---
src/test/regress/sql/json.sql | 6 ++--
7 files changed, 144 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c76d357..e7338b5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10277,6 +10277,28 @@ table2-mapping
<row>
<entry>
<indexterm>
+ <primary>json_array_elements_text</primary>
+ </indexterm>
+ <literal>json_array_elements_text(json)</literal>
+ </entry>
+ <entry><type>SETOF json</type></entry>
+ <entry>
+ Expands a JSON array to a set of JSON values. The returned value will be of
+ type text.
+ </entry>
+ <entry><literal>json_array_elements_text('["foo", "bar"]')</literal></entry>
+ <entry>
+<programlisting>
+ value
+-----------
+ foo
+ bar
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry>
+ <indexterm>
<primary>json_typeof</primary>
</indexterm>
<literal>json_typeof(json)</literal>
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 90fa447..b8e64f3 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -66,6 +66,9 @@ static void each_object_field_end(void *state, char *fname, bool isnull);
static void each_array_start(void *state);
static void each_scalar(void *state, char *token, JsonTokenType tokentype);
+/* common worker for json_each* functions */
+static inline Datum elements_worker(PG_FUNCTION_ARGS, bool as_text);
+
/* semantic action functions for json_array_elements */
static void elements_object_start(void *state);
static void elements_array_element_start(void *state, bool isnull);
@@ -157,6 +160,9 @@ typedef struct ElementsState
TupleDesc ret_tdesc;
MemoryContext tmp_cxt;
char *result_start;
+ bool normalize_results;
+ bool next_scalar;
+ char *normalized_scalar;
} ElementsState;
/* state for get_json_object_as_hash */
@@ -1061,7 +1067,7 @@ each_scalar(void *state, char *token, JsonTokenType tokentype)
}
/*
- * SQL function json_array_elements
+ * SQL function json_array_elements and json_array_elements_text
*
* get the elements from a json array
*
@@ -1070,10 +1076,22 @@ each_scalar(void *state, char *token, JsonTokenType tokentype)
Datum
json_array_elements(PG_FUNCTION_ARGS)
{
+ return elements_worker(fcinfo, false);
+}
+
+Datum
+json_array_elements_text(PG_FUNCTION_ARGS)
+{
+ return elements_worker(fcinfo, true);
+}
+
+static inline Datum
+elements_worker(PG_FUNCTION_ARGS, bool as_text)
+{
text *json = PG_GETARG_TEXT_P(0);
- /* elements doesn't need any escaped strings, so use false here */
- JsonLexContext *lex = makeJsonLexContext(json, false);
+ /* elements only needs escaped strings when as_text */
+ JsonLexContext *lex = makeJsonLexContext(json, as_text);
JsonSemAction *sem;
ReturnSetInfo *rsi;
MemoryContext old_cxt;
@@ -1116,6 +1134,9 @@ json_array_elements(PG_FUNCTION_ARGS)
sem->array_element_start = elements_array_element_start;
sem->array_element_end = elements_array_element_end;
+ state->normalize_results = as_text;
+ state->next_scalar = false;
+
state->lex = lex;
state->tmp_cxt = AllocSetContextCreate(CurrentMemoryContext,
"json_array_elements temporary cxt",
@@ -1138,7 +1159,17 @@ elements_array_element_start(void *state, bool isnull)
/* save a pointer to where the value starts */
if (_state->lex->lex_level == 1)
- _state->result_start = _state->lex->token_start;
+ {
+ /*
+ * next_scalar will be reset in the array_element_end handler, and
+ * since we know the value is a scalar there is no danger of it being
+ * on while recursing down the tree.
+ */
+ if (_state->normalize_results && _state->lex->token_type == JSON_TOKEN_STRING)
+ _state->next_scalar = true;
+ else
+ _state->result_start = _state->lex->token_start;
+ }
}
static void
@@ -1150,7 +1181,7 @@ elements_array_element_end(void *state, bool isnull)
text *val;
HeapTuple tuple;
Datum values[1];
- static bool nulls[1] = {false};
+ bool nulls[1] = {false};
/* skip over nested objects */
if (_state->lex->lex_level != 1)
@@ -1159,10 +1190,23 @@ elements_array_element_end(void *state, bool isnull)
/* use the tmp context so we can clean up after each tuple is done */
old_cxt = MemoryContextSwitchTo(_state->tmp_cxt);
- len = _state->lex->prev_token_terminator - _state->result_start;
- val = cstring_to_text_with_len(_state->result_start, len);
+ if (isnull && _state->normalize_results)
+ {
+ nulls[0] = true;
+ values[0] = (Datum) NULL;
+ }
+ else if (_state->next_scalar)
+ {
+ values[0] = CStringGetTextDatum(_state->normalized_scalar);
+ _state->next_scalar = false;
+ }
+ else
+ {
+ len = _state->lex->prev_token_terminator - _state->result_start;
+ val = cstring_to_text_with_len(_state->result_start, len);
+ values[0] = PointerGetDatum(val);
+ }
- values[0] = PointerGetDatum(val);
tuple = heap_form_tuple(_state->ret_tdesc, values, nulls);
@@ -1196,10 +1240,9 @@ elements_scalar(void *state, char *token, JsonTokenType tokentype)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("cannot call json_array_elements on a scalar")));
- /*
- * json_array_elements always returns json, so there's no need to think
- * about de-escaped values here.
- */
+ /* supply de-escaped value if required */
+ if (_state->next_scalar)
+ _state->normalized_scalar = token;
}
/*
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index ab05c46..9a1ce79 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4159,6 +4159,8 @@ DATA(insert OID = 3961 ( json_populate_recordset PGNSP PGUID 12 1 100 0 0 f f
DESCR("get set of records with fields from a json array of objects");
DATA(insert OID = 3968 ( json_typeof PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "114" _null_ _null_ _null_ _null_ json_typeof _null_ _null_ _null_ ));
DESCR("get the type of a json value");
+DATA(insert OID = 3969 ( json_array_elements_text PGNSP PGUID 12 1 100 0 0 f f f f t t i 1 0 114 "114" "{114,25}" "{i,o}" "{from_json,value}" _null_ json_array_elements_text _null_ _null_ _null_ ));
+DESCR("elements of json array");
/* uuid */
DATA(insert OID = 2952 ( uuid_in PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2950 "2275" _null_ _null_ _null_ _null_ uuid_in _null_ _null_ _null_ ));
diff --git a/src/include/utils/json.h b/src/include/utils/json.h
index 25bfafb..347305b 100644
--- a/src/include/utils/json.h
+++ b/src/include/utils/json.h
@@ -47,6 +47,7 @@ extern Datum json_array_length(PG_FUNCTION_ARGS);
extern Datum json_each(PG_FUNCTION_ARGS);
extern Datum json_each_text(PG_FUNCTION_ARGS);
extern Datum json_array_elements(PG_FUNCTION_ARGS);
+extern Datum json_array_elements_text(PG_FUNCTION_ARGS);
extern Datum json_populate_record(PG_FUNCTION_ARGS);
extern Datum json_populate_recordset(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index a8c45b3..cbb3819 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -801,7 +801,7 @@ select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
(1 row)
-- array_elements
-select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
+select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
json_array_elements
-----------------------
1
@@ -810,9 +810,10 @@ select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'
null
{"f1":1,"f2":[7,8,9]}
false
-(6 rows)
+ "stringy"
+(7 rows)
-select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q;
+select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
value
-----------------------
1
@@ -821,7 +822,32 @@ select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},
null
{"f1":1,"f2":[7,8,9]}
false
-(6 rows)
+ "stringy"
+(7 rows)
+
+select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
+ json_array_elements_text
+--------------------------
+ 1
+ true
+ [1,[2,3]]
+
+ {"f1":1,"f2":[7,8,9]}
+ false
+ stringy
+(7 rows)
+
+select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
+ value
+-----------------------
+ 1
+ true
+ [1,[2,3]]
+
+ {"f1":1,"f2":[7,8,9]}
+ false
+ stringy
+(7 rows)
-- populate_record
create type jpop as (a text, b int, c timestamp);
diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out
index 753e5b3..e67ab4d 100644
--- a/src/test/regress/expected/json_1.out
+++ b/src/test/regress/expected/json_1.out
@@ -801,7 +801,7 @@ select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
(1 row)
-- array_elements
-select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
+select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
json_array_elements
-----------------------
1
@@ -810,9 +810,10 @@ select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'
null
{"f1":1,"f2":[7,8,9]}
false
-(6 rows)
+ "stringy"
+(7 rows)
-select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q;
+select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
value
-----------------------
1
@@ -821,7 +822,32 @@ select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},
null
{"f1":1,"f2":[7,8,9]}
false
-(6 rows)
+ "stringy"
+(7 rows)
+
+select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
+ json_array_elements_text
+--------------------------
+ 1
+ true
+ [1,[2,3]]
+
+ {"f1":1,"f2":[7,8,9]}
+ false
+ stringy
+(7 rows)
+
+select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
+ value
+-----------------------
+ 1
+ true
+ [1,[2,3]]
+
+ {"f1":1,"f2":[7,8,9]}
+ false
+ stringy
+(7 rows)
-- populate_record
create type jpop as (a text, b int, c timestamp);
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index cd7782c..1c98c69 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -265,8 +265,10 @@ select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
-- array_elements
-select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
-select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q;
+select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
+select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
+select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
+select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
-- populate_record
create type jpop as (a text, b int, c timestamp);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
