Where to put Postgres json_each?

Postgres has several “set-returning” functions that essentially return more than one row. Probably the most well-known is generate_series, used to get a list of numbers/dates/etc. Another very handy one is json_each. You can use it to break up a JSON object into one row for each key/value pair. But that one always trips me up, because unlike generate_series it (1) returns more than one column per row and (2) usually takes a column as input.

Suppose you have a users table with a preferences column, like so:

t=# select * from users;
 id |                        preferences                         
----+------------------------------------------------------------
  1 | {"lang": "en", "time_zone": "America/Los_Angeles"}
  2 | {"lang": "es", "time_zone": "America/Cayman", "volume": 5}

If you do this you get tuples:

t=# select json_each(preferences) from users;
               json_each               

---

 (lang,"""en""")
 (time_zone,"""America/Los_Angeles""")
 (lang,"""es""")
 (time_zone,"""America/Cayman""")
 (volume,5)

It would be nicer to get separate key and value columns. That works great if json_each appears in the FROM clause:

t=# select * from json_each('{"lang": "en", "time_zone": "America/Los_Angeles"}');
    key    |         value         
-----------+-----------------------
 lang      | "en"
 time_zone | "America/Los_Angeles"

But if you replace the literal with a column, it breaks:

t=# select key, value from json_each(users.preferences);
ERROR:  missing FROM-clause entry for table "users"
LINE 1: select key, value from json_each(users.preferences);

This doesn’t work either:

t=# select key, value from (select json_each(preferences) from users) x(key, value);
ERROR:  table "x" has 1 columns available but 2 columns specified

So what to do? Well here is something that works, although I find it hard to remember:

t=# select id, (json_each(preferences)).* from users;
 id |    key    |         value         
----+-----------+-----------------------
  1 | lang      | "en"
  1 | time_zone | "America/Los_Angeles"
  2 | lang      | "es"
  2 | time_zone | "America/Cayman"
  2 | volume    | 5

Basically you are decomposing the tuple. You can pick your columns too:

t=# select id, (json_each(preferences)).key, (json_each(preferences)).value from users;
 id |    key    |         value         
----+-----------+-----------------------
  1 | lang      | "en"
  1 | time_zone | "America/Los_Angeles"
  2 | lang      | "es"
  2 | time_zone | "America/Cayman"
  2 | volume    | 5

Possibly nicer is this trick about set-returning functions allowing an implicit lateral join:

t=# select id, key, value from users, json_each(preferences);
 id |    key    |         value         
----+-----------+-----------------------
  1 | lang      | "en"
  1 | time_zone | "America/Los_Angeles"
  2 | lang      | "es"
  2 | time_zone | "America/Cayman"
  2 | volume    | 5
(5 rows)

So if you’re struggling to use json_each with a non-literal input, there you go!

blog comments powered by Disqus Prev: Postgres Permissions Next: When the Inner JSON Effect Works