Jsonb_set with OrmLite

cthames · January 11, 2020, 1:42am

What do you recommend to do using OrmLite for doing a DB UPDATE where the SET uses jsonb_set() function?

Note: Just a single prop/key is being updated, not multiple or deep nested props/key. So nothing overly complex.

create table my_table(j jsonb);
-- sets an array to the 'a' prop/key of the jsonb object. (does not overwrite it)
update my_table set j = jsonb_set(j,'{a}','[2,3,4]');

The issue is that a normal OrmLite UpdateOnly<T>() will overwrite the entire jsonb object which can lead to data loss if updates are not done in order.

-- NOT THIS - I don't want to overwrite the property, as it would lose any other info
update my_table set j = '{"a":[2,3,4]}';

I was initially going to create a separate IDbCommand extension similar to OrmLite’s WriteExpressionCommandExtensions.UpdateOnly<T>() that would be able to build an update with jsonb_set().

However, there are several internal methods/extensions that make this impossible without modifying the actual OrmLite codebase.

Example: OrmLiteConfigExtensions.GetModelDefinition(this Type modelType)

So, what do you recommend to do, to code an extension that will work with OrmLite in order to use jsonb_set()?

It looks like a lot of duplicate code would need to happen in order to use OrmLiteDialectProvider*.

github.com

ServiceStack/ServiceStack.OrmLite/blob/0b2523fbda6b2428ff1f2337fb108c5779b022a1/src/ServiceStack.OrmLite/Expressions/WriteExpressionCommandExtensions.cs#L39-L51


      
          internal static int UpdateOnly<T>(this IDbCommand dbCmd, T obj,
              Expression<Func<T, object>> onlyFields = null,
              Expression<Func<T, bool>> where = null,
              Action<IDbCommand> commandFilter = null)
          {
              if (onlyFields == null)
                  throw new ArgumentNullException(nameof(onlyFields));
          
              var q = dbCmd.GetDialectProvider().SqlExpression<T>();
              q.Update(onlyFields);
              q.Where(where);
              return dbCmd.UpdateOnly(obj, q, commandFilter);
          }

github.com

ServiceStack/ServiceStack.OrmLite/blob/88f7424e824264dda3dbffc6d71e1ddfcd1e7072/src/ServiceStack.OrmLite/OrmLiteDialectProviderBase.cs#L1066-L1108


      
          public virtual void PrepareUpdateRowStatement(IDbCommand dbCmd, object objWithProperties, ICollection<string> updateFields = null)
          {
              var sql = StringBuilderCache.Allocate();
              var sqlFilter = StringBuilderCacheAlt.Allocate();
              var modelDef = objWithProperties.GetType().GetModelDefinition();
              var updateAllFields = updateFields == null || updateFields.Count == 0;
          
              foreach (var fieldDef in modelDef.FieldDefinitions)
              {
                  if (fieldDef.ShouldSkipUpdate())
                      continue;
          
                  try
                  {
                      if (fieldDef.IsPrimaryKey && updateAllFields)
                      {
                          if (sqlFilter.Length > 0)
                              sqlFilter.Append(" AND ");
          
                          sqlFilter

This file has been truncated. show original

github.com

ServiceStack/ServiceStack.OrmLite/blob/9ffb9147b0402b39c033098a7b24a376782dcdbb/src/ServiceStack.OrmLite/OrmLiteConfigExtensions.cs#L43


      
                  return true;

              }

              return false;

          }

          

          internal static void ClearCache()

          {

              typeModelDefinitionMap = new Dictionary<Type, ModelDefinition>();

          }

          

          internal static ModelDefinition GetModelDefinition(this Type modelType)

          {

              if (typeModelDefinitionMap.TryGetValue(modelType, out var modelDef))

                  return modelDef;

          

              if (modelType.IsValueType || modelType == typeof(string))

                  return null;

          

              var modelAliasAttr = modelType.FirstAttribute<AliasAttribute>();

              var schemaAttr = modelType.FirstAttribute<SchemaAttribute>();

mythz · January 11, 2020, 9:10pm

All non-standard SQL like PostgreSQL’s json functions would need to be done with Custom SQL which you would execute with ExecuteSql/Async API.

cthames · January 12, 2020, 6:24pm

Yeah, I was trying to do something that would be able to use on multiple tables that would allow me using the SS OrmLite ModelDefinition to build the SQL update so that any changes wouldn’t affect the SQL or have to code for many Custom SQL for each data model that do the same thing.

Currently almost every table has at least one json/jsonb field, so creating custom sql for each and keeping it up to date when data models change (it’s a new project) is a little tedious.

With json fields apart of every db and them being using more frequently with NoSQL type storage and SPA interacting with them, situations where data overwrites (if sql column update) can happen on json fields is increasing.

If I did have a way to get jsonb_set using OrmLite and posted it, is that something you would think about putting in OrmLite for everyone? Most db’s have some sort of json_modify function.

The only thing is that you’d probably want it to work for all situations (deep/nested objects) and not just first level keys. Though I’m not sure if I have the time to go that far. You probably wouldn’t include it if it didn’t work in those nested scenarios?

mythz · January 12, 2020, 11:46pm

It would need to depend on what the Typed API looked like, whether it’s a good fit for OrmLite and intuitive to use.

It’s ok if it only worked for partial situations as long as support for more complete implementation doesn’t break backwards compatibility of existing APIs, if it might it should be marked with [Obsolete] until we’re confident on an API that’s future proofed.