[PATCH] Implement json_array_elements_text
Started by Laurence Rowealmost 12 years ago4 messages
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(-)
Attachments:
0001-Implement-json_array_elements_text.patchtext/x-patch; charset=US-ASCII; name=0001-Implement-json_array_elements_text.patchDownload
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);
Re: [PATCH] Implement json_array_elements_text
On 01/20/2014 09:58 PM, Laurence Rowe wrote:
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,
Can we sneak this very small feature into 9.4? I'm happy to take on the
review etc.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Implement json_array_elements_text
On 20 January 2014 18:58, Laurence Rowe <l@lrowe.co.uk> wrote:
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.
This updated patch makes the return type of ``json_array_elements_text``
text rather than json, I'd not set it correctly in pg_proc.h.
Laurence
Attachments:
0001-Implement-json_array_elements_text.patchapplication/octet-stream; name=0001-Implement-json_array_elements_text.patchDownload
From cbd6a16dcc4082574ac2971252f36d74cc0d46d9 Mon Sep 17 00:00:00 2001
From: Laurence Rowe <laurence@lrowe.co.uk>
Date: Mon, 20 Jan 2014 20:48:38 -0800
Subject: [PATCH] Implement json_array_elements_text
---
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..648fe8b 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 25 "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);
--
1.7.11.3
Re: [PATCH] Implement json_array_elements_text
On 01/20/2014 10:34 PM, Andrew Dunstan wrote:
On 01/20/2014 09:58 PM, Laurence Rowe wrote:
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,Can we sneak this very small feature into 9.4? I'm happy to take on
the review etc.
I'm going to take silence as consent and try to get the updated version
of this committed today.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers