Postgresql path for json array

From: Saravanakumar Murugesan <saravana(dot)kumar(at)overturenetworks(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Postgresql path for json array
Date: 2015-08-31 10:46:06
Message-ID: B574F8F44CBD374B95823E8C858EA47A44713D9A@mbx024-e1-nj-2.exch024.domain.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a json for country

Table column: countryJson

Sample Json : [ { name:india,
population: 1000M,
type: country,
children: [
{
name: karnataga,
population: 30M,
type:state,
children: [ {
name: bangalore,
population: 10M,
type:district },
{
name: mysore,
population: 2M,
type:district

},
]
}
]
}]

I need to index country name, state name and district name. I need to find districts with population greater than 5M. How to query districts with statename = karnataga and country name = india?

i.e select countryJson[ name =''india"].children[name="karnataga"].children [population] > 5m?

Is it supported in postgresql? i.e better examples on array value indexes and array value criteria would help me to use easily use json.

cheers
Saravanakumar

This email and attachments may contain privileged or confidential information intended only for the addressee(s) indicated. The sender does not waive any of its rights, privileges or protections respecting this information. If you are not the named addressee, an employee, or agent responsible for sending this message to the named addressee (or this message was received by mistake), you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If received in error, please notify us immediately by e-mail, discard any paper copies and delete all electronic files of the email.

Computer viruses can be transmitted via email. The recipient should check this email and any attachments for viruses. Email transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender accepts no liability for any damage caused by any transmitted viruses or errors or omissions in the contents of this message.

Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560 www.overturenetworks.com

Browse pgsql-general by date

  From Date Subject
Next Message Saravanakumar Murugesan 2015-08-31 10:55:18 FW: JsonArray value criteria
Previous Message Charles Clavadetscher 2015-08-31 07:23:26 Re: Public facing PostgreSQL hosting ?