Thread: [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(-)

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

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