json_array_elements_text?

From: Laurence Rowe <l(at)lrowe(dot)co(dot)uk>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: json_array_elements_text?
Date: 2014-01-17 08:20:04
Message-ID: CAOycyLQcR=Fr6QzLpe_AL10oVPxkpA-jA1jniZ7JuAsZsSf30w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm trying to unpack a json array into it's constituent text values so I
can join them to a table. I can successfully unpack json values, but am
having trouble converting these to text so I can cast them to the UUIDs
needed for the join.

http://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-arraysuggest
using trim, and this does work:

SELECT trim(json_array_elements(biosample.properties->'treatments')::text,
'"')::uuid
FROM object biosample WHERE biosample.item_type = 'biosample'

But it seems kinda ugly. Am I missing something obvious or is postgres 9.3
just missing a json_array_elements_text function?

Laurence

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sameer Kumar 2014-01-17 09:16:55 Re: Correct query to check streaming replication lag
Previous Message Chris Travers 2014-01-17 07:29:37 Re: PostgreSQL with ZFS on Linux