How to query my JSON data if I am currently storing it as a String in the database?

[dario-vega]

Use case: I have JSON data that was stored in a string format inadvertently while inserting/updating rows. The operation succeeded because the string data is a valid JSON. However, the fields are populated with the unparsed string.

It seems strange, but it happens very often, especially when receiving data from 3rd parties.

Let's take a look at the following data

select * from discussions;
{
  "id" : 1,
  "jsonString" : "{\"doc\":{\"title\":\"Using parse_json\"}}",
  "jsonWithStrings" : {
    "doc" : "{\"title\":\"Using parse_json\"}"
  },
  "fullJson" : {
    "doc" : {
      "title" : "Using parse_json"
    }
  }
}

Here, the jsonString field is populated as a complete string. The jsonWithStrings field has a doc attribute, which is JSON, however, the value is a string instead of a JSON. The fullJson field is a proper JSON document.

Updating to have a more complex JSON example - adding Author and site

upsert into discussions values (1, 
                             '{"doc":{"title":"Using parse_json", "author":"Dario", "site":"github.com/oracle/nosql-examples"}}', 
                              {"doc":'{"title":"Using parse_json", "author":"Dario", "site":"github.com/oracle/nosql-examples"}'}, 
                              {"doc":{"title":"Using parse_json", "author":"Dario", "site":"github.com/oracle/nosql-examples"}}
) RETURNING *;

{
  "id" : 1,
  "jsonString" : "{\"doc\":{\"title\":\"Using parse_json\", \"author\":\"Dario\", \"site\":\"github.com/oracle/nosql-examples\"}}",
  "jsonWithStrings" : {
    "doc" : "{\"title\":\"Using parse_json\", \"author\":\"Dario\"} , \"site\":\"github.com/oracle/nosql-examples\""
  },
  "fullJson" : {
    "doc" : {
      "author" : "Dario",
      "site" : "github.com/oracle/nosql-examples",
      "title" : "Using parse_json"
    }
  }
}

Okay, but how to query my data using JSON queries? Simply use the parse_json function

select id  
      , parse_json(t.jsonString).doc.title as title_v1
      , parse_json(t.jsonString).doc.author as author_v1
      , parse_json(t.jsonWithStrings.doc).title as title_v2
      , parse_json(t.jsonWithStrings.doc).author as author_v2
      , t.fullJson.doc.title as title_v3
      , t.fullJson.doc.author as author_v3
from discussions t;

{
  "id" : 1,
  "title_v1" : "Using parse_json",
  "author_v1" : "Dario",
  "title_v2" : "Using parse_json",
  "author_v2" : "Dario",
  "title_v3" : "Using parse_json",
  "author_v3" : "Dario"
}

Without using parse_json

select id  
      , t.jsonString.doc.title as title_v1
      , t.jsonWithStrings.doc.title as title_v2
      , t.fullJson.doc.title  as title_v3
from discussions t;

{
  "id" : 1,
  "title_v1" : null,
  "title_v2" : null,
  "title_v3" : "Using parse_json"
}

Of course, the best practice is always to store JSON as JSON and not as Strings or mix both inside a JSON, but sometimes it is happening. By the way, you can use this function in the DDL statements to parse and insert it as JSON.

[dario-vega]

What if I have this error?

select parse_json(t.jsonWithStrings.doc).title  from discussions  t;

Error: at (1, 36) Cannot promote item of type :
{
  "type" : "MAP",
  "collection" : {
    "type" : "JSON"
  }
}
to type :
String

It means that your data are already stored as JSON. Or maybe you are mixing documents with String values and JSON values.

It seems really strange, but it could happen too.

select CASE
        WHEN t.jsonWithStrings.doc IS OF (String)
        THEN parse_json(t.jsonWithStrings.doc)
        ELSE t.jsonWithStrings.doc
       END as doc
from discussions t;