Postgres: Parsing JSON data in sql

Question

I am trying to parse following json data and use it under the where clause.

Basic":{  
   "General":{  
      "Field1":1234,
      "Field2":"6.86"
   },
   "Stream 0":{  
      "Type":"LDAP",
      "Field4":"LALA1"
    },
    "Stream 1":{  
      "Type":"KERBEROS",
      "Field4":"LALA2"
    },
    "Stream 2":{  
      "Type":"SAML",
      "Field4":"LALA3"
    },

I can reach to Type tag like this.
table.column_json::json->'Basic'->'Stream 0'->'Type'

The order of Stream # is undefined and there could more than 3 Stream tags.

How can I iterate through each Stream and find if Type: is "SAML" then Field4 value is "LALA3".

Patrick · Accepted Answer · 2016-04-30 04:53:35Z

1

Since you have multiple different keys you want to search through, you need to "unpack" the K/V pairs and then do string matching:

SELECT DISTINCT t.*
FROM table t, json_each(t.column_json::json->'Basic') j(k, v)
WHERE j.k LIKE 'Stream%'
  AND j.v->>'Type' = 'SAML'
  AND j.v->>'Field4' = 'LALA3';

Note that the json_each() function is PG9.3+.

edited Apr 30, 2016 at 4:53

answered Apr 29, 2016 at 16:49

Patrick

32.9k6 gold badges73 silver badges102 bronze badges

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

2 Comments

Himanshu Yadav Over a year ago

json_each() worked for me but = are giving operator does not exist error. I tried using ` j.v->'Type'::varchar='SAML'` but no luck.

Patrick Over a year ago

Ah, yes. It should have been the ->> operator to get the field as text. See updated answer.

Collectives™ on Stack Overflow

Postgres: Parsing JSON data in sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related