Skip to content

Instantly share code, notes, and snippets.

@trozdol

trozdol/DB2 SQL Search and Filter Clob JSON Data.sql

Created January 9, 2021 23:06
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save trozdol/120335f24df8f00299eb0004bae093e7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save trozdol/120335f24df8f00299eb0004bae093e7 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
DB2 SQL Search and Filter Clob JSON Data.sql
-- params: I hate everything about this.
-- 1,2,3) SORT BY : CONTAINER_TYPE
-- 4) OBJECT_TYPE : ITEM_HAZMAT
-- 5) COLUMN TO SEARCH : ITEM_NO
-- 6) VALUE TO SEARCH FOR : 00876773
SELECT
OBJECT_TYPE,
OBJECT_ID_VALUE,
KEY,
(REPLACE(SUBSTR(JSON, (INSTR(JSON, '":', KEY_LOC, 1) + 2), ((INSTR(JSON, ',', KEY_LOC, 1) - INSTR(JSON, '":', KEY_LOC, 1)) - 2)), '"', '')) AS VAL,
RES.*
FROM (
---------------------------------------------------------------
SELECT
STAGING.*,
LOCATE('' || ?, JSON) AS KEY_LOC,
SUBSTR(JSON, LOCATE('' || ?, JSON), LENGTH('' || ?)) AS KEY
FROM (
---------------------------------------------------------------
SELECT
OBJ.OBJECT_STAGING.*,
CAST(CAST(OBJECT_REP AS CLOB(6000)) AS VARCHAR(6000)) AS JSON
FROM
OBJ.OBJECT_STAGING
---------------------------------------------------------------
) STAGING
WHERE
OBJECT_TYPE = ?
---------------------------------------------------------------
) RES
WHERE
JSON LIKE '%"' || ? || '":%' || ? || '%,%'
ORDER BY
VAL ASC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment