Viewing a json table in adminer as a (sortable) table
I have a jsonb field in a database table, I'd like to be able to save it as a view in Adminer so that I can quickly search and sort the fields like with a standard database table.
I wonder if the json-column adminer plugin could help, but I can't work out how to use it.
I'm using the adminer docker image which I believe has plugins already built in.
If I have a database like this (somebody kindly put together this fiddle for a previous question)
CREATE TABLE prices( sub_table JSONB );
INSERT INTO prices VALUES
('{ "0": {"Name": "CompX", "Price": 10, "index": 1, "Date": "2020-01-09T00:00:00.000Z"},
"1": {"Name": "CompY", "Price": 20, "index": 1, "Date": "2020-01-09T00:00:00.000Z"},
"2": {"Name": "CompX", "Price": 19, "index": 2, "Date": "2020-01-10T00:00:00.000Z"}
}');
and to view a subset of the table
SELECT j.value
FROM prices p
CROSS JOIN jsonb_each(sub_table) AS j(e)
WHERE (j.value -> 'Name')::text = '"CompX"'
I'd like to see the following table in Adminer
| Date | Name | Price | index |
| ------------------------ | ----- | ----- | ----- |
| 2020-01-09T00:00:00.000Z | CompX | 10 | 1 |
| 2020-01-10T00:00:00.000Z | CompX | 19 | 2 |
| | | | |
as opposed to:
| value |
| ------------------------------------------------------------ |
| {"Date": "2020-01-09T00:00:00.000Z", "Name": "CompX", "Price": 10, "index": 1} |
| {"Date": "2020-01-09T00:00:00.000Z", "Name": "CompX", "Price": 10, "index": 1} |
EDIT - building on a-horse-with-no-name answer. The following saves a view with the appropriate columns, and can then be searched/sorted in Adminer in the same way as a standard table.
CREATE VIEW PriceSummary AS
select r.*
from prices p
cross join lateral jsonb_each(p.sub_table) as x(key, value)
cross join lateral jsonb_to_record(x.value) as r("Name" text, "Price" int, index int, "Date" date)
There is no automatic conversion available, but you could convert the JSON value to a record, to make the display easier:
select r.*
from prices p
cross join lateral jsonb_each(p.sub_table) as x(key, value)
cross join lateral jsonb_to_record(x.value) as r("Name" text, "Price" int, index int, "Date" date)
where r."Name" = 'CompX'