Viewing a postgresql json table in adminer as a (sortable) table

Question

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)

I find the name `sub_table` for a _column_ highly confusing. — , Aug 19 '20 at 20:08

score 2 · Accepted Answer · answered Aug 19 '20 at 20:12

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'

Online example

score 1 · Answer 2 · edited Aug 19 '20 at 20:06

1

The json-column adminer plugin will do the job to some extent, in the sense that it will display that JSON values better.

Here is a minimal docker-compose.yml that creates a postgres conatiner and links an adminer to it. To have plugins installed in the adminer container, you can use the environment variable ADMINER_PLUGINS as shown below:

version: '3'
services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example

  adminer:
    image: adminer
    restart: always
    environment:
      - ADMINER_PLUGINS=json-column tables-filter tinymce
    ports:
      - 9999:8080

Access the adminer web UI at localhost:9999. Use username: postgres, password: example to connect to the postgres database.

When you will edit a table that contains JSON columns, it will be displayed like this:

edited Aug 19 '20 at 20:06

answered Aug 19 '20 at 16:25

Neo Anderson

5,957
2
12
29

Thanks, that works as in your picture. Ideally I want that view transposed so that I can have sortable columns for Date, Name, Price, Index – ceharep Aug 19 '20 at 18:07
You can leverage the fact that you are storing the JSONs as JSONB. [Here](https://www.blendo.co/blog/storing-json-in-postgresql/) is a quick guide about how can you take advantage of that. Just for the purpose of viewing them in adminer, I believe that rewriting that plugin or developing a new one is overkill – Neo Anderson Aug 19 '20 at 19:01

Viewing a postgresql json table in adminer as a (sortable) table

2 Answers2