How do I query using fields inside the new PostgreSQL JSON datatype

Question

I am looking for some docs and or examples for the new JSON functions in PostgreSQL 9 2    Specifically  given a series of JSON records        name   Toby   occupation   Software Engineer       name   Zaphod   occupation   Galactic President       How would I write the SQL to find a record by name   In vanilla SQL   SELECT   from json data WHERE  name     Toby    The official dev manual is quite sparse    http   www postgresql org docs devel static datatype-json html http   www postgresql org docs devel static functions-json html   Update I  I ve put together a gist detailing what is currently possible with PostgreSQL 9 2  Using some custom functions  it is possible to do things like   SELECT id  json string data  name   FROM things WHERE json string data  name   LIKE  G      Update II  I ve now moved my JSON functions into their own project   PostSQL - a set of functions for transforming PostgreSQL and PL v8 into a totally awesome JSON document store

User · Answer

With Postgres 9 3   just use the - gt  operator  For example   SELECT data- gt  images - gt  thumbnail - gt  url  AS thumb FROM instagram   see http   clarkdave net 2013 06 what-can-you-do-with-postgresql-and-json  for some nice examples and a tutorial

User · Answer

With postgres 9 3 use -  for object access  4 example  seed rb  se   SmartElement new se data          params                              type  1              code  1              value  2012              description   year of producction                                   type  1              code  2              value  30              description   length                     se save   rails c  SELECT data- gt  params - gt 0 as data FROM smart elements    returns                                   data ----------------------------------------------------------------------    type  1  code  1  value  2012  description   year of producction    1 row    You can continue nesting   SELECT data- gt  params - gt 0- gt  type  as data FROM smart elements    return   data ------  1  1 row

User · Answer

Postgres 9 2  I quote Andrew Dunstan on the pgsql-hackers list      At some stage there will possibly be some json-processing  as opposed   to json-producing  functions  but not in 9 2    Doesn t prevent him from providing an example implementation in PLV8 that should solve your problem   Postgres 9 3  Offers an arsenal of new functions and operators to add  json-processing     The manual on new JSON functionality  The Postgres Wiki on new features in pg 9 3   Will posted a link to a blog demonstrating the new operators in a comments below    The answer to the original question in Postgres 9 3   SELECT   FROM   json array elements        name    Toby    occupation    Software Engineer          name    Zaphod    occupation    Galactic President          AS elem WHERE elem- gt  gt  name     Toby     Advanced example    Query combinations with nested array of records in JSON datatype   For bigger tables you may want to add an expression index to increase performance    Index for finding an element in a JSON array   Postgres 9 4  Adds jsonb  b for  binary   values are stored as native Postgres types  and yet more functionality for both types  In addition to expression indexes mentioned above  jsonb also supports GIN  btree and hash indexes  GIN being the most potent of these    The manual on json and jsonb data types and functions  The Postgres Wiki on JSONB in pg 9 4   The manual goes as far as suggesting      In general  most applications should prefer to store JSON data as   jsonb  unless there are quite specialized needs  such as legacy   assumptions about ordering of object keys    Bold emphasis mine   Performance benefits from general improvements to GIN indexes   Postgres 9 5  Complete jsonb functions and operators  Add more functions to manipulate jsonb in place and for display    Major good news in the release notes of Postgres 9 5

[sql] How do I query using fields inside the new PostgreSQL JSON datatype?

Examples related to sql

Examples related to json

Examples related to postgresql

Examples related to postgresql-9.2

Examples related to postgresql-9.3