0

I have this json column in my postgres v 10.18 database.

[{"name":"Dan","adresse":"+41 22 222 22 22, Dan@mail.com"},{"name":"Dagmar","adresse":"031 300 300, Yes@gmail.com"}]

Better readable.

[
  {
    "name": "Dan",
    "adresse": "+41 22 222 22 22, Dan@mail.com"
  },
  {
    "name": "Dagmar",
    "adresse": "031 300 300, Yes@gmail.com"
  }
]

The json list contacts and i can be 0 or more contacts inside. I want to get max 5 contacts in columns like this.

enter image description here

Does anyone has a nice solution for this?

user2210516
  • 613
  • 3
  • 15
  • 32
  • Does this answer your question? [PostgresSql - Split aggregated, comma separated values into separate columns iniside query - with Amazon Aws & PostgreSql 9.6](https://stackoverflow.com/questions/51583383/postgressql-split-aggregated-comma-separated-values-into-separate-columns-ini) – Filburt Oct 08 '21 at 07:13
  • Unrelated but maybe it's worth reviewing the table design? – Stefanov.sm Oct 08 '21 at 08:28

1 Answers1

1

You can chain the -> operators:

select the_column -> 0 ->> 'name' as contact_1, 
       the_column -> 0 ->> 'adresse' as contact_info_1,
       the_column -> 1 ->> 'name' as contact_2, 
       the_column -> 1 ->> 'adresse' as contact_info_2,
       the_column -> 2 ->> 'name' as contact_3, 
       the_column -> 2 ->> 'adresse' as contact_info_3
from the_table

the_column -> 0 picks the first array element, ->> 'name' then retrieves the value of the key as text

  • select k.kontakte -> 0 ->> 'name'::text as Kontakt_1, k.kontakte -> 0 ->> 'adresse'::text as Kontaktdaten_1, k.kontakte -> 1 ->> 'name'::text as Kontakt_2, k.kontakte -> 1 ->> 'adresse'::text as Kontaktdaten_2, k.kontakte -> 2 ->> 'name'::text as Kontakt_3, k.kontakte -> 2 ->> 'adresse'::text as Kontaktdaten_3 from personen k where id = 188714 Gives me following error: SQL Error [42883]: ERROR: operator does not exist: character varying -> integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 24 – user2210516 Oct 08 '21 at 08:19
  • 2
    `k.kontakte` type is `text`, right? As the error message hints, replace it with `k.kontakte::json` in the query above. – Stefanov.sm Oct 08 '21 at 08:32
  • @user2210516: you stated that the column is a `json` column. Apparently that's not the case, so you will need to cast it. Or better changed the column to be a `jsonb` column. –  Oct 08 '21 at 08:36