0

In a Postgres 12 database, I have a simple schema containing a JSON column. The JSON will always contain an object, and inside the object there are always 14 keys like so:

CREATE TABLE json_unpacking (foo text, bar json);
INSERT INTO json_unpacking VALUES
(
'ziggy',
'{"Baz (1998-03-12)":1.4285714286,"Baz (1998-03-13)":7.1428571429,"Baz (1998-03-14)":10.8571428571,"Baz (1998-03-15)":11.2857142857,"Baz (1998-03-16)":12.0,"Baz (1998-03-17)":18.1428571429,"Baz (1998-03-18)":2.0,"Baz (1998-03-19)":2.5714285714,"Baz (1998-03-20)":12.2857142857,"Baz (1998-03-21)":3.2857142857,"Baz (1998-03-22)":5.5734285714,"Baz (1998-03-23)":4.1428571429,"Baz (1998-03-24)":11.2857142857,"Baz (1998-03-25)":6.0}'
),
(
'zoggy',
'{"Baz (1998-04-12)":1.7285714286,"Baz (1998-04-13)":6.8578886549,"Baz (1998-04-14)":6.8574428571,"Baz (1998-04-15)":21.2857142857,"Baz (1998-04-16)":7.0,"Baz (1998-04-17)":3.1128571429,"Baz (1998-04-18)":5.0,"Baz (1998-04-19)":7.2214285714,"Baz (1998-04-20)":42.6857142857,"Baz (1998-04-21)":2.9857142857,"Baz (1998-04-22)":5.5734285714,"Baz (1998-04-23)":4.1428571429,"Baz (1998-03-24)":6.7537146517,"Baz (1998-04-25)":47.0}'
)
;

I'm able to query this table just fine, for instance:

SELECT *
FROM json_unpacking

Produces the output that you'd expect:

foo bar
ziggy {"Baz (1998-03-12)":1.4285714286,"Baz (1998-03-13)":7.1428571429,"Baz (1998-03-14)":10.8571428571,"Baz (1998-03-15)":11.2857142857,"Baz (1998-03-16)":12.0,"Baz (1998-03-17)":18.1428571429,"Baz (1998-03-18)":2.0,"Baz (1998-03-19)":2.5714285714,"Baz (1998-03-20)":12.2857142857,"Baz (1998-03-21)":3.2857142857,"Baz (1998-03-22)":5.5734285714,"Baz (1998-03-23)":4.1428571429,"Baz (1998-03-24)":11.2857142857,"Baz (1998-03-25)":6.0}
zoggy {"Baz (1998-04-12)":1.7285714286,"Baz (1998-04-13)":6.8578886549,"Baz (1998-04-14)":6.8574428571,"Baz (1998-04-15)":21.2857142857,"Baz (1998-04-16)":7.0,"Baz (1998-04-17)":3.1128571429,"Baz (1998-04-18)":5.0,"Baz (1998-04-19)":7.2214285714,"Baz (1998-04-20)":42.6857142857,"Baz (1998-04-21)":2.9857142857,"Baz (1998-04-22)":5.5734285714,"Baz (1998-04-23)":4.1428571429,"Baz (1998-03-24)":6.7537146517,"Baz (1998-04-25)":47.0}

Now, I'd like to query this table and produce a structure with 15 total columns: "foo" plus 14 like "bar 1", "bar 2", ... "bar 14".

The JSON structure will always include a string pattern (which includes the date), and a numeric value. I do not want the key value, but I need the number values transposed into columns.

In other words, the output structure that I want is like so:

foo bar 1 bar 2 bar 3 bar 4 bar 5 bar 6 bar 7 bar 8 bar 9 bar 10 bar 11 bar 12 bar 13 bar 14
ziggy 1.428571429 7.142857143 10.85714286 11.28571429 12 18.14285714 2 2.571428571 12.28571429 3.285714286 5.573428571 4.142857143 11.28571429 6
zoggy 1.728571429 6.857888655 6.857442857 21.28571429 7 3.112857143 5 7.221428571 42.68571429 2.985714286 5.573428571 4.142857143 6.753714652 47

I suppose I have two questions:

  1. Is this possible?
  2. Can it be done without any extension?

A colleague has suggested that the crosstab() function from the tablefunc extension might be required. I've tried various Postgres JSON functions, but have only succeeded in getting either the keys (e.g. using json_object_keys()) or null values (using json_to_record()).

Here's a DBfiddle of the problem: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=22e9c99a954eafdadcde657b38f41b3a

1

You can use a JSON path query:

select foo, 
       items ->> 0 as bar_1, 
       items ->> 1 as bar_2, 
       items ->> 1 as bar_3, 
       .....
from ( 
  select foo, jsonb_path_query_array(bar::jsonb, '$.*') as items
  from json_unpacking
) as t 

Online example


Note that it's recommended to use jsonb over json (and in this case it would avoid the cast)

1
  • Thank you so much, this was very helpful! I'll add that this style also allows use of multiple JSON columns, e.g. ... jsonb_path_query_array(bar::jsonb, '$.*') as bar, jsonb_path_query_array("bar baz"::jsonb, '$.*') as bar_baz, ...
    – blong
    Jan 4 at 18:10
0

Unpack your JSON to separate rows using

SELECT json_unpacking.foo, 
       values.value, 
       ROW_NUMBER() OVER (PARTITION BY foo) value_number
FROM json_unpacking
CROSS JOIN json_each(json_unpacking.bar) values

Then combine it back into needed row structure using grouping and conditional aggregation.

1
  • Thank you so much @Akina . Ultimately, I used the other solution, but the answer you provided is very interesting and I'm sure it will help many others.
    – blong
    Jan 4 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.