[PATCH] Implement json_array_elements_text
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+144-22
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
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+144-23
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