1

I have a table dictionary and its structure is:

create table dictionary
(
  id integer,
  first_level_key character varying(50),
  key_name character varying(50),
  key_value_en character varying(1000),
  key_value_fr character varying(1000)
);

I want to output this table as well-formed indented JSON.

I have tried the following query:

COPY (select 
   (select row_to_json(_) from (select c.first_level_key) as _) as first_last,
   c.key_name,c.key_name_value_en
from
   dictionary as c) 
 TO '/tmp/label_en.json';

I got JSON output, but not in the correct format, as shown below.

{"first_level_key":"topheader"} screen  Screen Reader Access
{"first_level_key":"header"}    title 
{"first_level_key":"header"}    subtitle 
{"first_level_key":"nav"}   about   About

The output that I want is well-formed, indented JSON as shown below!

{
"topheader":{

        "screen":"Screen Reader Access"
        },
        "header":{
        "title":"title",
        "subtitle":"subtitletest"
        },
    "nav":{
        "about":"About"
}}
1

While rereading the documentation, I noticed that whereas the pretty_bool option for JSON doesn't have an indented option, there is a jsonb_pretty(from_json jsonb) function which does produce indented text (Returns from_json as indented JSON text.) which looked as if it is what you are looking for.

When you have a "normal" table (I used your DDL in the question as my template), what you have to do is first convert it to JSON using the ROW_TO_JSON function (documentation), but as discussed here and elsewhere, this only converts the table to JSON on a row-by-row basis. The JSONB_AGG function (docs here) aggregates values, including nulls, as a JSON array).

The final piece of the puzzle is the JSONB_PRETTY (discussed above) function which takes the JSON produced and outputs indented JSON text using your original query as the inner loop of the query.

Fiddle available here (and all DDL and DML is at the bottom of this answer).

SELECT JSONB_PRETTY(JSONB_AGG(tab)) AS i_json FROM
(
  SELECT
  ( 
    SELECT ROW_TO_JSON(x) 
    FROM 
    (
      SELECT c.first_level_key) AS x
    ) AS first_last,
    c.key_name, c.key_value_en
  FROM
  dictionary AS c
) AS tab;

Sample output (entire table at end of this answer):

[
    {
        "key_name": "ttttttt",
        "first_last": {
            "first_level_key": "afasaffasa"
        },
        "key_value_en": "zxccc"
    },

I believe that this is (finally!) the correct answer!

When asking a question, please always provide your version - it can be very important for providing an answer.

DDL and DML (also on the fiddle):

Create table. Note that I used the TEXT datatype instead of VARCHAR(n) for reasons discussed here and here.

CREATE TABLE dictionary
(
  id INTEGER,
  first_level_key TEXT,
  key_name TEXT,
  key_value_en TEXT,
  key_value_fr TEXT
);

Populate it:

INSERT INTO dictionary 
VALUES 
(4, 'afasaffasa', 'ttttttt', 'zxccc', 'zzzzzzz'),
(4, 'afasawweww', 'rrrrrrr', 'zxccc', 'zzzzzzz'),
(4, 'afayyyyyyy', 'eeeeeee', 'zxccc', 'zzzzzzz'),
(4, 'xxxxxxxxxx', 'wwwwwww', 'zxccc', 'zzzzzzz'),
(4, 'mmmmmmmmmm', 'qqqqqqq', 'zxccc', 'zzzzzzz');

Run query:

SELECT JSONB_PRETTY(JSONB_AGG(tab)) AS i_json FROM
(
  SELECT
  ( 
    SELECT ROW_TO_JSON(x) 
    FROM 
    (
      SELECT c.first_level_key) AS x
    ) AS first_last,
    c.key_name, c.key_value_en
  FROM
  dictionary AS c
) AS tab;

Result:

i_json
[
    {
        "key_name": "ttttttt",
        "first_last": {
            "first_level_key": "afasaffasa"
        },
        "key_value_en": "zxccc"
    },
    {
        "key_name": "rrrrrrr",
        "first_last": {
            "first_level_key": "afasawweww"
        },
        "key_value_en": "zxccc"
    },
    {
        "key_name": "eeeeeee",
        "first_last": {
            "first_level_key": "afayyyyyyy"
        },
        "key_value_en": "zxccc"
    },
    {
        "key_name": "wwwwwww",
        "first_last": {
            "first_level_key": "xxxxxxxxxx"
        },
        "key_value_en": "zxccc"
    },
    {
        "key_name": "qqqqqqq",
        "first_last": {
            "first_level_key": "mmmmmmmmmm"
        },
        "key_value_en": "zxccc"
    }
]
| improve this answer | |
  • It is not coming as correct formate as you have created you could see the Json key_name always coming` "key_name": "wwwwwww",` inside of name we need values like this { "topheader":{ "screen":"Screen Reader Access" }, "header":{ "title":"title", "subtitle":"subtitletest" }, "nav":{ "about":"About" }} – Abhinaw Anand Feb 27 at 7:57
  • Is something like this more what you're looking for? – Vérace Feb 29 at 17:50
  • Is something like this more what you're looking for? If not, can you provide some sample data in the form of DML? – Vérace Feb 29 at 18:27
  • Lots of Thanks for your valuable time (dbfiddle.uk/…) in this Json output having all ` "ttttttt":` – Abhinaw Anand Mar 3 at 5:32
  • Mr. @Verace please check this query here I have briefly explain the Json output should be in the formate – Abhinaw Anand Mar 3 at 6:23
0

The output that I want is well-formed, indented JSON

  • Well-formed JSON is what you get from the function you're already using, by which I mean "some text" that it syntactically valid as JSON.

  • Indented JSON is something that is "pretty" and human-readable.

I have to ask why you feel the need for the latter?

JSON is a Data Transfer medium.
As such, I would not expect "average" Users to be interacting with it directly, so why should any JSON data need to be "bloated out" with all that redundant and processing time-consuming white-space that the format itself has no use for?

| improve this answer | |
  • You might be interested in this - an answer (Python) on more or less the same topic as the above - it has 1561 upvotes - so, obviously people are interested in producing human-readable JSON! I just hope my answer gets that many upvotes! :-) – Vérace Feb 27 at 6:12
  • yes you are right Mr. @Vérace the answer is given by Mr. Andy (6th) answer is very close enough – Abhinaw Anand Mar 3 at 5:49
  • If you've managed to translate the Python into SQL and obtained the result you want, then please post that code here - I, for one, will upvote that answer! :-) p.s. calling any answer the "6th" one can be tricky - answers can move up or down depending on votes - the poster's name should be enough! – Vérace Mar 3 at 6:54
0

This query I'm using for getting the Json output. This may be help for other. Here you could found the all steps

SELECT 
    jsonb_object_agg(key, value)
FROM (
    SELECT
        jsonb_build_object(first_level_key, jsonb_object_agg(key, value)) as language
    FROM (
          SELECT
                first_level_key,
                jsonb_build_object(first_level_key_name, key_value_en) AS first_level_key_name
            FROM
                dictionary
            GROUP BY first_level_key, first_level_key_name,key_value_en order by first_level_key desc
        ) s, 
        jsonb_each(first_level_key_name)

    GROUP BY first_level_key
) s,
jsonb_each(language)
| improve this answer | |

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.