Question 89769 in PostgreSQL Question posted in
The official documentation can be found here.

Find authors with age under 40 in my postgresql table

I have the following table with one row. I have tried to query this table to return authors under the age of 40 and have been unable to do so.

CREATE TABLE bookstuff (
  data JSON
);


insert into bookstuff(data)
VALUES('
{
  "the_books": {
    "publishers": [
      {
        "name": "Dunder Mifflin",
        "address": "Scranton, PA",
        "country": "USA",
        "CEO": "David Wallace"
      },
      {
        "name": "Sabre",
        "address": "Tallahassee, FL",
        "country": "USA",
        "CEO": "Jo Bennett"
      },
      {
        "name": "Michael Scott Paper company",
        "address": "Scranton, PA",
        "country": "USA",
        "CEO": "Michael Gary Scott"
      },
      {
        "name": "Vance Refrigeration",
        "address": "Scranton, PA",
        "country": "USA",
        "CEO": "Bob Vance"
      }
    ],
    "authors": [
      {
        "id": 1,
        "name": "Michael Scott",
        "age": 45,
        "country": "USA",
        "agentname": "Jan Levinson",
        "books_written": "book1"
      },
      {
        "id": 2,
        "name": "Jim Halpert",
        "age": 35,
        "country": "USA",
        "agentname": "Pam Beesly",
        "books_written": "book3"
      },
      {
        "id": 3,
        "name": "Dwight Schrute",
        "age": 40,
        "country": "USA",
        "agentname": "Angela Martin",
        "books_written": "book2"
      },
      {
        "id": 4,
        "name": "Pam Halpert",
        "age": 35,
        "country": "USA",
        "agentname": "Angela Martin",
        "books_written": "book4"
      }
    ],
    "books": [
      {
        "isbn": "book1",
        "title": "The Dundies",
        "price": 10.99,
        "year": 2005,
        "publishername": "Dunder Mifflin"
      },
      {
        "isbn": "book2",
        "title": "Bears, Beets, Battlestar Galactica",
        "price": 8.99,
        "year": 2006,
        "publishername": "Dunder Mifflin"
      },
      {
        "isbn": "book3",
        "title": "The Sabre Store",
        "price": 12.99,
        "year": 2007,
        "publishername": "Sabre"
      },
      {
        "isbn": "book4",
        "title": "Branch Wars",
        "price": 14.99,
        "year": 2015,
        "publishername": "Sabre"
      }
    ]
  }
}');

I have tried the following query to get the author’s age

SELECT data->'the_books'->'authors'
FROM bookstuff
WHERE (data->'the_books'->'authors'->>'age')::integer > 40;

I expect it to return two values ‘Jim halpert’ and ‘pam halpert’ but instead I get no result back, not even null.

I have also tried this query, just to see if i could get anything back at all from the table and still no results:

SELECT data->'the_books'->'authors'
FROM bookstuff
where (data->'the_books'->'authors'->'name')::jsonb ? 'Michael Scott';

I’m new to postgresql, is there a different way I should be going about this?

Tags:

2

Answers


  1. 0

    Using json_array_elements:

    select (v -> 'name')#>>'{}' from bookstuff b 
cross join json_array_elements(b.data -> 'the_books' -> 'authors') v
where ((v -> 'age')#>>'{}')::int < 40

    See fiddle

    Login or Signup to reply.
  2. 0

    Another option, slightly more verbose:

    select distinct(author->>'name') as author_name from 
(select json_array_elements(b.data->'the_books'->'authors') author  from bookstuff b) author
where (author->>'age')::int < 40

    The distinct might be unnecessary if you really just have one database row and no duplicates in the authors array of that row.

    Three considerations of why your final solution doesn’t work

    1. where filters out rows – this happens before the ‘select’. the row contains everything in this case.
    2. ‘?’ predicate matches an array containing your choosen value "Does the key/element string exist within the JSON value?" You don’t have a simple array here array->’key’ doesn’t pick that attribute into a new array
    3. your select projection isn’t called however it was it would contain the whole array (remember where doesn’t transform just filters out rows)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search