Extracting multiple fields from one column

Question

Post reply

Extracting multiple fields from one column

JeremyU

SSChasing Mays

Points: 640
More actions
May 26, 2023 at 7:03 pm

#4198354
Hello,
I have a situation.
I am trying to show each distinct event category, event action and event label from 'Hits' column and show the number of times each event occurred and the month that the event
occurred– for labels containing “GNAV”.
I am not sure why but something is wrong. Do you have any ideas what potentially can be wrong?
SELECT DISTINCT
SUBSTRING(HITS,
CHARINDEX(''eventCategory':' ',HITS)+1,
CHARINDEX(' ,' ',message)-CHARINDEX(' ', ',HITS)-1) as eventCategory,
COUNT(SUBSTRING(HITS,
CHARINDEX(''eventAction':' ',HITS)+1,
CHARINDEX(' ,' ',message)-CHARINDEX(' ', ',HITS)-1)) as eventAction,
COUNT(SUBSTRING(HITS,
CHARINDEX(''eventLabel':' ',HITS)+1,
CHARINDEX(' ,' ',message)-CHARINDEX(' ', ',HITS)-1)) as eventLabel,
substring('date', 3, 2) AS 'Month'
FROM TABLE
WHERE HITS LIKE '%GNAV%'
GROUP BY date
- This topic was modified 1 year, 6 months ago by JeremyU.
- This topic was modified 1 year, 6 months ago by JeremyU.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1380 More actions · Answer 1

Can you provide DDL & sample data insert script?

You said you want counts, but you're using DISTINCT & you're not returning COUNT()

JeremyU SSChasing Mays Points: 640 More actions · Answer 2

I cannot give DDL and sample data. Only that small part pretty much that I gave in the first message and yes, you are correct, I need to write COUNT before each extraction of the value. I believe I have corrected.

ratbak Ten Centuries Points: 1380 More actions · Answer 3

You generally don't need/want to use DISTINCT when you're using an aggregate like COUNT() -- you instead want to GROUP BY the SELECT columns that are not aggregated.

Does that work? If not, can your be more specific about the error/problem than "something is wrong"?

Based on the image, the data appears to almost be json: This would work:

{"eventInfo":{"eventCategory": "GNAV-Furniture-Living_Room","eventAction": "click", "eventLabel": "GNAV : Sofas & Sectionals"}}

If valid json, a json-oriented query might be much simpler than the string parsing -- e.g.,

DROP TABLE IF EXISTS #events;

CREATE TABLE #events
(
VisitID INT NOT NULL PRIMARY KEY,
Hits NVARCHAR(max) NOT NULL,
[date] DATE NOT NULL
);

INSERT INTO #events
VALUES (15692456,'{"eventInfo":{"eventCategory": "GNAV-Furniture-Living_Room","eventAction" : "click", "eventLabel" : "GNAV : Sofas & Sectionals"}}','20220201')
-- or SELECT * FROM https://www.sqlservercentral.com/wp-content/uploads/hm_bbpui/4198354/zuiqcst2islse2b21pqavdeiz2zpgyqp.jpg if you want to try selecting from a picture instead of data :-)

SELECT 
    JSON_VALUE(Hits,'$.eventInfo.eventCategory') AS EventCategory,
    JSON_VALUE(Hits,'$.eventInfo.eventAction') AS EventAction,
    JSON_VALUE(Hits,'$.eventInfo.eventLabel') AS EventLabel
FROM #events;

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

JeremyU wrote:

I cannot give DDL and sample data. Only that small part pretty much that I gave in the first message and yes, you are correct, I need to write COUNT before each extraction of the value. I believe I have corrected.

At least stop posting data as graphics. And you could also turn that bit into some "Readily Consumable" data. It's been explained to you many times, Jeremy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)