Get nested objects values from JSON in Postgres

Question

So here is my JSON column in my Postgres DB:

{
  "objekt_art": {
    "86": {
      "code": "86",
      "bezeichnung_de": "Kino",
      "bezeichnung_fr": "Cinéma",       
      "bezeichnung_it": "Cinema",
      "bezeichnung_en": null,
      "kurz_bezeichnung_de": "Kino",
      "relevant_fuer_berechnung_steuerquote": true
    },
    "27": {
      "code": "27",
      "bezeichnung_de": "Kiosk",
      "bezeichnung_fr": "Kiosque",
      "bezeichnung_it": "Chiosco",
      "bezeichnung_en": null,
      "kurz_bezeichnung_de": "Kiosk",
      "relevant_fuer_berechnung_steuerquote": true
    }
  }
}

I need to be able to query the bezechnung_de for example where code = 86. The number of code i can pass from another table.

How can i for example make a query with two columns. One with the number and the second with bezeichnung_de.

Like this:

Code Bez
86   Kino

This would be so much easier with a properly normalized data model — user330315
– user330315, Commented Nov 3, 2021 at 6:53
Which datatype is used in a column jsonb or json or text? — Pooya
– Pooya, Commented Nov 3, 2021 at 6:58
Postgres 10.8 and it's a text field so i have to cast it to json or jsonb — user2210516
– user2210516, Commented Nov 3, 2021 at 7:06

user330315 · Accepted Answer · 2021-11-03 07:04:57Z

6

As you know the code, this is fairly easy:

select t.the_column -> 'objekt_art' -> '86' ->> 'code' as code,
       t.the_column -> 'objekt_art' -> '86' ->> 'bezeichnung_de' as bez
from the_table t
where ...

The value '86' can be a parameter. The first expression to select the code isn't really needed though, as you could replace it with the constant value (=parameter) directly.

If the "outer" JSON key isn't the same value as the code value, you could use something like this:

select o.value ->> 'code' as code, 
       o.value ->> 'bezeichnung_de' as bez
from the_table t
  cross join jsonb_each(t.the_column -> 'objekt_art') o(key, value)
where o.key = '86'
  and ... other conditions ...

If you are using Postgres 13 or later, this can also be written as a JSON path expression:

select a.item ->> 'code' as code,
       a.item ->> 'bezeichnung_de' as bez
from (
  select jsonb_path_query_first(t.the_column, '$.objekt_art.* ? (@.code == "86")') as item
  from the_table t
  where ....
) a

All examples assume that the column is defined with the data jsonb which it should be. If it's not you need to cast it: the_column::jsonb

answered Nov 3, 2021 at 7:04

user330315

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

4 Comments

user2210516 Over a year ago

yes but the problem is i don't know the 86 before. I have another table that have a column (objekt_art) with the number inside. I have to be able to make some kind of join with that value and the code value.

user330315 Over a year ago

@user2210516: you will have to compare the value 86 at some point. See my edit for alternatives. I find the JSON structure highly questionable because it repeats the same "code value" at two different locations. Accessing keys that you don't know the value for is a bit challenging. This would be all so much easier with a properly normalized data model.

user2210516 Over a year ago

yes i think i would be able to solve the problem alone if it wasnt for the double location of the numbers. Thanks for your help

user330315 Over a year ago

If you can be sure that the duplicated values are never out of sync, the first query will work just fine.

Pooya · Accepted Answer · 2021-11-03 08:15:15Z

5

Sample data structure and sample table for join data: dbfiddle

select
  je.value -> 'code' as "Code",
  je.value -> 'bezeichnung_de' as "Bez"
from
  test t
  cross join jsonb_each((data::jsonb ->> 'objekt_art')::jsonb) je
  -- In table test_join I insert value 86 for join record
  inner join test_join tj on je.key::int = tj.json_id

answered Nov 3, 2021 at 8:15

Pooya

3,2032 gold badges16 silver badges21 bronze badges

1 Comment

user330315 Over a year ago

(data::jsonb ->> 'objekt_art')::jsonb can be simplified to data::jsonb -> 'objekt_art'

gekh · Accepted Answer · 2024-05-28 07:36:24Z

0

For me works this way.

SELECT data#>>'{objekt_art, 86, code}'

docs: https://www.postgresql.org/docs/current/functions-json.html

Look for Extracts JSON sub-object at the specified path as text.

answered May 28, 2024 at 7:36

gekh

2,21222 silver badges19 bronze badges

Collectives™ on Stack Overflow

Get nested objects values from JSON in Postgres

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related