Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.
Untested:
create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
begin
perform obj->'';
exception
when invalid_parameter_value then
return false;
when others then
raise;
end;
return true;
end;
$$;
This'll be pretty inefficient, since it's creating a sub-transaction for each call.
Or you could just update to 9.4.
On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.
test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
?column?
----------
(1 row)
... and the same with using ->> to dereference a scalar.
So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.
You might need to adapt it to use the same expression you use to trigger your reported error.
SSIS is, indeed, clunky when it comes to importing data for all the processing prowess that it seems to tout. One of the commenters, Kin I believe, had it right with creating a temporary table. Here's the code we use to import data directly from Excel into a temporary table to be parsed for compliance. As you can see in the example below, using T-SQL, OPENROWSET reads the specific tab on the Excel Spreadsheet called SQL Server Instance Details and imports the data into the SQL Table TempServerMap.
After, the data is imported into the temporary table you have a world of options. You can insert the data into another table that has all the SQL business rules you need on each of the columns--kicking out the data that doesn't qualify--I say this not as a necessarily "best way" but for using the least amount of code in the beginning fine tuning your process of finding errors. You'd need to control the error handling here to catch the bad apples (rows) so you can review them later. Alternately, you could create functions, or procedures, or triggers or all of the above that check the data in advance to make sure the do comply with business requirements.
And if you're looking to communicate those errors to others, just create an SSRS (Reporting Services) report that queries your new error table and give them the proper permissions to view the report. It's web based and easily accessible in an intranet environment. If you have external associates you can have SSRS e-mail the report to them just as easily.
The main point here is: get your data away from Excel and into SQL Server so it can be manipulated effectively.
Some DBAs might be offended at the replace/exec of the code below. If that's the case, just hardcode the select statement with specific excel file names.
declare @cmdstring varchar(8000) ='select * into SQLSunSet.dbo.TempServerMap from OPENROWSET (''Microsoft.Ace.OLEDB.12.0'', ''Excel 12.0;Database=<MapsExcelFile_SQLServerUsageTracker>'', ''select * from [SQL Server Instance Details$]'')'
set @cmdstring = replace(@cmdstring,'<MapsExcelFile_SQLServerUsageTracker>',@MapsExcelFile_SQLServerUsageTracker)
if exists (select * from sysobjects where type ='u' and name ='TempServerMap') drop table TempServerMap
--print (@cmdstring)
exec (@cmdstring)
Many people seem to turn to SSIS in hopes of not coding T-SQL. But I usually find that SSIS is like faulty four-wheel drive. It just get's you stuck more than you would have been a lot farther down the road.
P.S. to use OPENROWSET effectively you might need to install
the Microsoft Access Database Engine 2010 Redistributable (Really--a Microsoft Office Engine--not just for MS Access). This can be downloaded here:
https://www.microsoft.com/en-us/download/details.aspx?id=13255
Additionally, you might need to configure SQL Server to run OPENROWSET and also use the Office engine within T-SQL using the following code after it's installed:
exec sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Best Answer
The documentation you linked to links to the JSON page, which says:
Wait a second...I think this documentation needs a pull request :)
Anyways...As always with
MAX, if you don't need it, don't use it! You're correct that it will cause a (slight) performance hit and SQL Server in no way prefersNVARCHAR(MAX)for JSON storage. Consider yourself lucky you're storing such small JSON.