MongoSQLd - DRDL to convert object to json string in SQL column

Working with Data Connectors & Integrations
1

Is it possible to configure a drdl file so that mongosqld will convert an object to JSON and present it as varchar?

For example if this name/value is part of a document in Mongo:

        "recordData" : {
    		"subject" : {
    			"txtFirstName" : "Harley",
    			"txtLastName" : "Holland",
    			"dtBirthDate" : "1942-06-14",
    			"txtCity" : "Bedrock",
    			"txtState" : "Idaho",
    			"selGender" : "Male"
    		}
        }

I’d like to create a drdl entry that would convert the whole objecte to a json string within one sql column.

I can’t find a list of supported types in drdl. In the example below I’ve tried both object and string in place of bson.document and none work.

- Name: recordData
  MongoType: bson.document
  SqlName: recordData
  SqlType: varchar

Any pointers would help.

2

You can apply aggregate on the field using $objectToArray then $map each records into "key":"value" string then $reduce them into one big string.

{$set: {recordData: {$concat: [
  '{"subject":{',
  {$reduce: {
    input: {$objectToArray: '$recordData.subject'},
    initialValue: '',
    in: {$concat: [
      '$$value',
      {$cond: {if: {$eq: ['$$value', '']}, then: '', else: ','}},
      '"', '$$this.k' ,'":"', {$toString: '$$this.v'}, '"'
    ]}
  }},
  '}}'
]}}}

There are more universal processing by using $switch, $type, etc… But I will skip them in order to make this answer short.

1 Like