Question 397753 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql. Create output like JSON

I have table like next

Name
tblname_01
tblname_02
tblname_03 
CREATE TABLE test (
name varchar(128)
);

INSERT INTO test
VALUES
('tblname_01'),
('tblname_02'),
('tblname_03');

SELECT * FROM test;

I would like to get next output:

    {
      "rule-type": "selection",
      "rule-id": "1001",
      "rule-name": "1001",
      "object-locator": {
        "schema-name": "%",
        "table-name": "tblname_01"
      },
      "rule-action": "include",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "1002",
      "rule-name": "1002",
      "object-locator": {
        "schema-name": "%",
        "table-name": "tblname_02"
      },
      "rule-action": "include",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "1003",
      "rule-name": "1003",
      "object-locator": {
        "schema-name": "%",
        "table-name": "tblname_03"
      },
      "rule-action": "include",
      "filters": []
    }

Actually, I need to create JSON-file from more then 100 names of tables. But, first tried to create JSON-files from three names of tables, but I didn’t.

Tags:

2

Answers


  1. 0

    Based on your data you could do something like this

    select
jsonb_build_array(array_agg( 
    jsonb_build_object( 
        'rule-type', 
        'selection', 
        'rule-id', split_part(name,'_',2)::int+1000,
        'rule-name', split_part(name,'_',2)::int+1000,
        'object-locator', 
        jsonb_build_object(
            'schema-name', '%', 
            'table-name', name
        ),
        'rule-action', 'include',
        'filters', array[]::text[]
    )
))
from test;

    Fiddle to test

    Login or Signup to reply.
  2. 0

    For the purposes of constructing a numerical sequence of rules, you can use generate_series and join the original table back on to it:

    select jsonb_agg(jsonb_build_object('rule-type', 'selection',
    'rule-id', (1000+n)::text,
    'rule-name', (1000+n)::text,
    'object-locator', jsonb_build_object('schema-name', '%', 'table-name', t1.name),
    'rule-action', 'include',
    'filters', jsonb_build_array()
)) 
from generate_series(1, (select count(*) from test)) n
join (select row_number() over (order by t.name) r, t.* from test t) t1
on t1.r = n

    See fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search