I've never used JSONB columns before, so I'm struggling with a simple query.

I need to select all the value fields from this json below. The output should be: value1, value2, value3, value4, value5, value6, value7, value8

That's as far as I got, but I could not find a way to go deeper in the json.

SELECT result -> 'report' ->> 'products' AS values FROM example_table

I appreciate your help.

CREATE TABLE example_table(
 id SERIAL PRIMARY KEY,
 result JSONB NOT NULL);

INSERT INTO example_table(result)
VALUES('{
  "report": {
    "products": [
      {
        "productName": "Product One",
        "types": [
          {
            "type": "Type One",
            "metadata": {
              "prices": [
                {
                  "price": {
                    "value": "value1"
                  }
                },
                {
                  "price": {
                    "value": "value2"
                  }
                }
              ]
            }
          },
          {
            "type": "Type Two",
            "metadata": {
              "prices": [
                {
                  "price": {
                    "value": "value3"
                  }
                },
                {
                  "price": {
                    "value": "value4"
                  }
                }
              ]
            }
          }
        ]
      },
      {
        "productName": "Product Two",
        "types": [
          {
            "type": "Type One",
            "metadata": {
              "prices": [
                {
                  "price": {
                    "value": "value5"
                  }
                },
                {
                  "price": {
                    "value": "value6"
                  }
                }
              ]
            }
          },
          {
            "type": "Type Two",
            "metadata": {
              "prices": [
                {
                  "price": {
                    "value": "value7"
                  }
                },
                {
                  "price": {
                    "value": "value8"
                  }
                }
              ]
            }
          }
        ]
      }
    ]
  }
}');

CodePudding user response：

You should use CROSS JOIN and jsonb_array_elements functions to extract array for each element of records

Demo

select
  et.id,
  string_agg(ptmp.value -> 'price' ->> 'value', ',')
from 
  example_table et
  cross join jsonb_array_elements(et.result -> 'report' -> 'products') p
  cross join jsonb_array_elements (p.value -> 'types') pt
  cross join jsonb_array_elements (pt.value -> 'metadata' -> 'prices') ptmp
group by et.id