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(-)
