Unpacking JSON into 14 columns without crosstab() and tablefunc? 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',
'{&quot;Baz (1998-03-12)&quot;:1.4285714286,&quot;Baz (1998-03-13)&quot;:7.1428571429,&quot;Baz (1998-03-14)&quot;:10.8571428571,&quot;Baz (1998-03-15)&quot;:11.2857142857,&quot;Baz (1998-03-16)&quot;:12.0,&quot;Baz (1998-03-17)&quot;:18.1428571429,&quot;Baz (1998-03-18)&quot;:2.0,&quot;Baz (1998-03-19)&quot;:2.5714285714,&quot;Baz (1998-03-20)&quot;:12.2857142857,&quot;Baz (1998-03-21)&quot;:3.2857142857,&quot;Baz (1998-03-22)&quot;:5.5734285714,&quot;Baz (1998-03-23)&quot;:4.1428571429,&quot;Baz (1998-03-24)&quot;:11.2857142857,&quot;Baz (1998-03-25)&quot;:6.0}'
),
(
'zoggy',
'{&quot;Baz (1998-04-12)&quot;:1.7285714286,&quot;Baz (1998-04-13)&quot;:6.8578886549,&quot;Baz (1998-04-14)&quot;:6.8574428571,&quot;Baz (1998-04-15)&quot;:21.2857142857,&quot;Baz (1998-04-16)&quot;:7.0,&quot;Baz (1998-04-17)&quot;:3.1128571429,&quot;Baz (1998-04-18)&quot;:5.0,&quot;Baz (1998-04-19)&quot;:7.2214285714,&quot;Baz (1998-04-20)&quot;:42.6857142857,&quot;Baz (1998-04-21)&quot;:2.9857142857,&quot;Baz (1998-04-22)&quot;:5.5734285714,&quot;Baz (1998-04-23)&quot;:4.1428571429,&quot;Baz (1998-03-24)&quot;:6.7537146517,&quot;Baz (1998-04-25)&quot;: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
{&quot;Baz (1998-03-12)&quot;:1.4285714286,&quot;Baz (1998-03-13)&quot;:7.1428571429,&quot;Baz (1998-03-14)&quot;:10.8571428571,&quot;Baz (1998-03-15)&quot;:11.2857142857,&quot;Baz (1998-03-16)&quot;:12.0,&quot;Baz (1998-03-17)&quot;:18.1428571429,&quot;Baz (1998-03-18)&quot;:2.0,&quot;Baz (1998-03-19)&quot;:2.5714285714,&quot;Baz (1998-03-20)&quot;:12.2857142857,&quot;Baz (1998-03-21)&quot;:3.2857142857,&quot;Baz (1998-03-22)&quot;:5.5734285714,&quot;Baz (1998-03-23)&quot;:4.1428571429,&quot;Baz (1998-03-24)&quot;:11.2857142857,&quot;Baz (1998-03-25)&quot;:6.0}


zoggy
{&quot;Baz (1998-04-12)&quot;:1.7285714286,&quot;Baz (1998-04-13)&quot;:6.8578886549,&quot;Baz (1998-04-14)&quot;:6.8574428571,&quot;Baz (1998-04-15)&quot;:21.2857142857,&quot;Baz (1998-04-16)&quot;:7.0,&quot;Baz (1998-04-17)&quot;:3.1128571429,&quot;Baz (1998-04-18)&quot;:5.0,&quot;Baz (1998-04-19)&quot;:7.2214285714,&quot;Baz (1998-04-20)&quot;:42.6857142857,&quot;Baz (1998-04-21)&quot;:2.9857142857,&quot;Baz (1998-04-22)&quot;:5.5734285714,&quot;Baz (1998-04-23)&quot;:4.1428571429,&quot;Baz (1998-03-24)&quot;:6.7537146517,&quot;Baz (1998-04-25)&quot;:47.0}




Now, I'd like to query this table and produce a structure with 15 total columns: &quot;foo&quot; plus 14 like &quot;bar 1&quot;, &quot;bar 2&quot;, ... &quot;bar 14&quot;.
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:

Is this possible?
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&amp;fiddle=22e9c99a954eafdadcde657b38f41b3a
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.