Printing an information nested in PostgreSQL json data

Question

The following PostgreSQL query produces an output in json format.

SELECT exampleColumn::jsonb FROM public."MyTable"

The output is as follows:

[
   {
      "testSettings":{
         "www.test1.com":{
            "IpAddress":[
               {
                  "ipv4Addr":"192.168.0.1",
                  "myId":"myIdExample"
               }
            ]
         },
         "www.try3.com":{
            "IpAddress":[
               {
                  "ipv4Addr":"192.168.0.5",
                  "myId":"myIdExample"
               }
            ]
         }
      },
      "testSettings":{
         "www.test6.com":{
            "IpAddress":[
               {
                  "ipv4Addr":"192.168.0.7",
                  "myId":"myIdExample"
               }
            ]
         },
         "www.try8.com":{
            "IpAddress":[
               {
                  "ipv4Addr":"192.168.0.4",
                  "myId":"myIdExample"
               }
            ]
         }
      }
   }
]

I'm aware that testSettings is not unique. However, values starting with 'www' may or may not be unique.

I want to get an output like this using this data:

| website           | ipv4Addr      |
|---------------    |-------------  |
| www.test1.com     | 192.168.0.1   |
| www.try3.com      | 192.168.0.5   |
| www.test6.com     | 192.168.0.7   |
| www.try8.com      | 192.168.0.4   |

I tried to use the example below for this. However, when I typed a query as

SELECT exampleColumn ::jsonb 
FROM public."MyTable" in values()

WITH c(j) AS (
  values(SELECT exampleColumn ::json 
         FROM public."MyTable")
)
SELECT j->jsonb_object_keys(j)->>'www*' 
FROM public."MyTable";

I got an error.

https://stackoverflow.com/a/48923352

It would be easier if the website names were the same. What could be our solutions for this issue? Thank you.

My approach would be to redesign the database to use a more relational data model, which would make the task simple. Sorry, I know that this isn't helpful... — Laurenz Albe
– Laurenz Albe, Commented Feb 28, 2023 at 6:52
That's non-standard JSON to begin with because your first (and only) array element contains the same key twice. jsonb will remove duplicate keys, so the second testSettings element will be lost. — user330315
– user330315, Commented Feb 28, 2023 at 7:15

adeviloper · Accepted Answer · 2023-02-28 08:08:25Z

0

The sample data you provided is not a standard JSON, there are duplicate keys. Assuming you're fixing that, you can get the website and its IP address using the below query.

SELECT KEY AS website,
              (value->'IpAddress'->0->>'ipv4Addr') AS ipv4Addr
FROM my_table,
     jsonb_each(example_column->0->'testSettings')
WHERE value->'IpAddress'->0->>'ipv4Addr' IS NOT NULL;

answered Feb 28, 2023 at 8:08

adeviloper

1792 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Printing an information nested in PostgreSQL json data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related