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

How to parse jsonb field into columns with if else PostgreSQL

I have a jsonb field attributes in my table. It looks like this –

{
    "id": "10",
    "customer": [
    {
      "id": "001",
      "name": "Customer1",
      "customerType": "internal"
    },
    {
      "id": "002",
      "name": "Customer2",
      "customerType": "external"
    }
  ]
}

The customer array can have only 2 objects in it. I want to check if customer[0][‘customerType’] or customer[1][‘customerType’] is internal then i want a column as internal and have the name in it.

Similarly if customer[0][‘customerType’] or customer[1][‘customerType’] is external then i want a column as external and have the name in it.

I tried parsing the field but not sure if such conditional logic is doable using just SQL –

SELECT (attributes->'customer'->0->'name1') as internal
    FROM table;

Any help is appreaciated.

Tags:

2

Answers


  1. 0

    You can use a JSON path query:

    select jsonb_path_query_first(attributes, '$.customer[*] ? (@.customerType == "internal")') ->> 'name' as internal,
       jsonb_path_query_first(attributes, '$.customer[*] ? (@.customerType == "external")') ->> 'name' as external
from the_table;
    Login or Signup to reply.
  2. 0

    Use a path expression

    select 
 (jsonb_path_query_array(tab.att, '$."customer"[*]?(@."customerType" == "internal")'::jsonpath) -> 0) #>> '{id}'::text[] AS internal_customer_id,
 (jsonb_path_query_array(tab.att, '$."customer"[*]?(@."customerType" == "external")'::jsonpath) -> 0) #>> '{id}'::text[] AS external_customer_id
from tab;

internal_customer_id|external_customer_id|
--------------------+--------------------+
001                 |002                 |

    Explanation

    jsonb_path_query_array(tab.att, '$."customer"[*]?(@."customerType" == "internal")'::jsonpath)

    returns the array elements of internal customers.

     [{"id": "001", "name": "Customer1", "customerType": "internal"}]

    You take the first one (index 0)

    (jsonb_path_query_array(tab.att, '$."customer"[*]?(@."customerType" == "internal")'::jsonpath)  -> 0) 

{"id": "001", "name": "Customer1", "customerType": "internal"}

    With #>> you extracts JSON sub-object at the specified path as text. Note that the path is casted as text[]

    (jsonb_path_query_array(tab.att, '$."customer"[*]?(@."customerType" == "internal")'::jsonpath)  -> 0) #>> '{id}'::text[] 

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

Back To Top
Search