extract specific returning field in json using postgres

Question

I have the following JSON :

{ "firstName": "John",
  "lastName": "Smith",
  "isAlive": true,
  "age": 27,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    },
    {
      "type": "mobile",
      "number": "123 456-7890"
    }
  ],
  "children": [],
  "spouse": "Maria"
}

and I am trying to get an array or a table of all the numbers the user have, some users have only 1 number and some have many, is there a way to call all "number" within the json

Could you give an example of what output you would like, and perhaps an "imagined" query (one that doesn't work/doesn't exist) that would give this output? — qff
– qff, Commented Dec 17, 2017 at 15:20

deinspanjer · Accepted Answer · 2017-12-17 15:32:51Z

1

I am going to try to answer this on my phone, so please forgive the brevity.

SELECT user->>'firstName' as first_name
  , user->>'lastName' as last_name
  , phone_type
  , phone_number
FROM users_tbl
CROSS JOIN LATERAL jsonb_array_elements(user->'phoneNumbers') jarr(num_obj)
CROSS JOIN LATERAL (
  SELECT num_obj->>'type' AS phone_type
    , num_obj->>'number' AS phone_number
  ) nums

answered Dec 17, 2017 at 15:32

deinspanjer

5551 gold badge8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

extract specific returning field in json using postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related