json and 9.3 function question

From: Rama <ramarro28(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: json and 9.3 function question
Date: 2013-11-13 14:08:52
Message-ID: CALUit-eTka-4xNmC8gbxKTo3RfoCTQ3+fZbqxkv9xVuwUEj2tA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

i would like to make a query using the new 9.3 json functionality agains a
json.

here a sample of data

{
"Data": {
"A": [
{
"B": { "b": "sample1" }
},
{
"B": [
{ "b": "sample2" },
{ "b": "sample3" }
]
}
]
}
}

and what i want to achive is

select * from table where set_of_attribute_b not have 'sample3'::varchar

i have do some test the main issue is that B is an object (1st) and an
array (2nd node)

with this query

select json_extract_path(json_array_elements(json_extract_path('{"Data": {
"A": [ { "B": { "b": "1" } },{ "B": [{ "b": "1" },{ "b": "2"
}]}]}}'::json,'Data','A')),'B')

i was able to extract two row, but the fist one is an object, the second is
an array.

i am not able to use json_array_elements, i got an error for the 1st
element begin an object rather than array, nor other approach.

any suggestion?

Browse pgsql-novice by date

  From Date Subject
Next Message Merlin Moncure 2013-11-13 15:09:07 Re: How to alias attributes in an ARRAY_AGG expression
Previous Message Claudio Poli 2013-11-13 08:50:15 How to alias attributes in an ARRAY_AGG expression