Home » Open Source » MySQL » JSON extract (MySQL)
JSON extract [message #666294] Thu, 26 October 2017 01:50 Go to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
Hi Sir,

A function has been created to parse JSON.
CREATE FUNCTION `fn_JSON_Extract`(
  details TEXT,
  required_field VARCHAR (256)
) RETURNS text CHARSET latin1
BEGIN
  RETURN TRIM(
    BOTH '"' FROM SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        SUBSTRING_INDEX(
          details,
          CONCAT(
            '"',
            SUBSTRING_INDEX(required_field,'$.', - 1),
            '"'
          ),
          - 1
        ),
        '",',
        1
      ),
      ':',
      - 1
    )
  ) ;
END

A JSON in this format
{"strength":"38","strength_uom":"mg","dose":"2","dose_uom":"day"}

When I am trying to extract the dose_uom as
CAST(fn_JSON_Extract(clinical_comments, 'strength_uom') AS char(2))
though it is extracting but while inserting into table with this value it is saying that it is trimming part of the strength_uom.
Is there any way to modify the function to get the desired result.I don't want to use common_schema feature.

Regards,
Samiran



Re: JSON extract [message #666319 is a reply to message #666294] Sat, 28 October 2017 04:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I am not seeing an error about trimming or any other error in my test below. Can you post a copy and paste, as I have done, of something similar that produces the error?


mysql> USE MYSQL
Database changed
mysql> DROP FUNCTION IF EXISTS fn_JSON_Extract;
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER $$
mysql> CREATE FUNCTION `fn_JSON_Extract`(
    ->   details TEXT,
    ->   required_field VARCHAR (256)
    -> ) RETURNS text CHARSET latin1
    -> BEGIN
    ->   RETURN TRIM(
    ->     BOTH '"' FROM SUBSTRING_INDEX(
    ->       SUBSTRING_INDEX(
    ->         SUBSTRING_INDEX(
    ->           details,
    ->           CONCAT(
    ->             '"',
    ->             SUBSTRING_INDEX(required_field,'$.', - 1),
    ->             '"'
    ->           ),
    ->           - 1
    ->         ),
    ->         '",',
    ->         1
    ->       ),
    ->       ':',
    ->       - 1
    ->     )
    ->   ) ;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> DROP TABLE test_tab;
Query OK, 0 rows affected (0.22 sec)

mysql> CREATE TABLE test_tab (test_col CHAR(2));
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO test_tab (test_col)
    ->   SELECT CAST(fn_JSON_Extract('{"strength":"38","strength_uom":"mg","dose":"2","dose_uom":"day"}', 'strength_uom') AS char(2));
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
Re: JSON extract [message #666369 is a reply to message #666319] Wed, 01 November 2017 07:09 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
I have used the function mentioned below but while inserting record it is giving me warning. I have used Substar to extract value and in this way I have resolved the problem of warning.

Regards,
Samiran
Re: JSON extract [message #666371 is a reply to message #666369] Wed, 01 November 2017 07:14 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
There is no problem to extract other value from the JSON only the last part dose_uom creating problem. I have used the query as
CREATE TABLE Patient_Medication as select hr.patient_id,hr.code_id as medication_code,
	(select code_name from PATIENT_DATA.Code where code_id = hr.code_id) as medication_name,
	CAST(fn_JSON_Extract(clinical_comments, ''strength'') AS SIGNED) as strength,
	CAST(fn_JSON_Extract(clinical_comments, ''strength_uom'') AS char(2)) as strength_uom,	
	concat(CAST(fn_JSON_Extract(clinical_comments, ''dose'') AS SIGNED),'' '',
	substr(CAST(fn_JSON_Extract(clinical_comments, ''dose_uom'') AS CHAR(5)),1,3)) as frequency,
	created_date as prescription_date
 from Health_Record 

Regards,
Samiran
Re: JSON extract [message #666384 is a reply to message #666371] Wed, 01 November 2017 15:07 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You still have not posted a copy and paste of a run of code that produces the error and shows the exact error. All you have posted is code. So, all I can guess, since it appears to be the last column, is that there me be a conflict in line feed characters between the operating system that the data comes from and the operating system you are trying to run the insert from. A portion of line feeds from one system may be perceived as just extra characters in another system. You can test this by loading that column into a larger field, checking the length, and using dump to see what is actually in it.
Previous Topic: Connecting to MSSQL server via php-pdo?
Next Topic: how to retrive all COUNTRY from a table
Goto Forum:
  


Current Time: Thu Mar 28 18:35:40 CDT 2024