0

I have a column PARAM that has JSON data that looks like this:

JSON:{"Name":"RES_InformationalIssues","StepName":"Page_1","StepData":"f601e9fe-d68a-4ed7-bbaa-f40c9cbc4d12=General Product Information","SessionId":"ab04f2c4-ddge-4555-c792-01d9518e7148"}

I would like to have multiple columns (Name, StepName, StepData, SessionID) with their data in them.

SELECT substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21) as Name, substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) as StepName FROM Table

That query won't work exactly right since I have changed the names but I think you get the picture. When I run the query for either of these columns I get the data I am looking for. But when they are run together as I have above, I get the error:

ERROR [HY000] ERROR: Record size 120000 exceeds internal limit of 65535 bytes

It feels like this means that I shouldn't assign the text of PARAM as the column name but that isn't what I am trying to do. Any suggestions or clarifications needed?

  • WHY are you storing JSON in a relational database? – Thomas Kejser Mar 15 '15 at 10:02
  • Thomas, I work in a big company with lots of people making lots of crazy choices. Doesn't it seem like I am trying to NOT store it as JSON from my question. – Skylar English Jul 24 '15 at 22:03
  • No, it looks like you are still storing something that is too close to JSON for comfort. Why not turn the name/value pairs into one column per JSON property and add the value of that property to that column. You may even be able to normalise most of this. If the JSON schema is not well defined, you could even go to a attribute/entity/value model for some simple cases. If you do, I don't see why you need the substr command to find things anymore – Thomas Kejser Jul 28 '15 at 18:30
0

I suppose the data type of the result of the function SUBSTR(PARAM, ...) has the same length as the column PARAM itself. Since you have two of those in your select list, if the length of PARAM is large enough it's possible that the combined length of two values in the select list exceeds some sort of internal limit.

Try explicitly casting the values to shorter data types, e.g.

SELECT 
  cast(
    substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21)
  as varchar (100)) as Name, 
  cast(
    substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) 
  as varchar (100)) as StepName 
FROM Table
-2

I am guesisng you are creating a table and you are maxing out the column/row size. break apart into more tables. if that is true.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.