What is the best way to store a whole JSON Object body in a table?

Question

Currently we are storing the JSON Object Body in a column which is [UsrJsonBody] [nvarchar](MAX) of a custom table. But sometime the whole JSON is not properly storing. That means for quite big JSONs the later part is getting missed. Can I know is there any issue with this approach. Please check the below code snippets. Thank you.

//Custom table creation
CREATE TABLE [dbo].[XXPOCreateHist](
	[ID] [int] IDENTITY(1,1),
	[CompanyID] [int] NOT NULL,
	[IntegrateDate] [datetime],
	[PONumber] [nvarchar](50),
	[IntegrateStatus] [nvarchar](2),
    [ErrorDesc] [nvarchar](256) ,
	[IntegrationType] [nvarchar](50),
	[UsrJsonBody] [nvarchar](MAX) ,....


//JSON Storing approach in the Graph file
var options = new JsonSerializerOptions { WriteIndented = true };
          var json=JsonSerializer.Serialize(obj,options);

          PXTrace.WriteInformation(json);
....

XXPOCreateHist toBeInserted = new XXPOCreateHist();

            toBeInserted.PONumber=order.OrderNbr;
            toBeInserted.IntegrateDate=DateTime.Now;
            toBeInserted.UsrJsonBody = json;

            XXPOCreateHistData.Insert(toBeInserted);
            XXPOCreateHistData.Cache.Persist(PXDBOperation.Update);

andriitkachenko · Accepted Answer

The nvarchar(max)field allowsstoringup to 2 GB of JSON. So unless your JSONs are THAT big - it might be the issue with attributes you’ve set on your DAC’s field.Please check the length you’ve set in the [PXDBString] attribute. If it’s defined like so: [PXDBString(1000)], Acumatica will automatically trim your JSON to 1000 characters. Leave it without specified length: [PXDBString].We are storing JSON responses for historical purposes this way in fields declared like so:#region F1[PXDBString][PXUIField(DisplayName = LabelConstants.F1)]public virtual string F1{ get; set; }public abstract class f1 : BqlString.Field<f1> { }#endregion F1Another point to consider is where you’re trying to check the field’s content. For example, SQL Server Management Studio and Workbench trimthe value by default (only in representation; the actual value can be stored properly, but for performance reasons won’t be shown in full). This might be the case for you.

malinthawarnakulasooriya08 · Answer

Thank you @andriitkachenkofor your valuable guidance.

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded