Generate rows before and after selected row from jsonb column-CodePudding

I want to ask for help because I can't solve this problem. I have this result which is correct. But it is the result of real physical data that are in the table.

SELECT row_number() OVER (ORDER BY PR."Priority") AS "LOS", "SpeedLessThan", "SpeedAtLeast"
 FROM "ProjectRules" PR
 WHERE PR."ProjectId" = 500
   AND PR."Group" = 1;

Well, the problem is, now I have a different structure where the physical line is JSON. And the result of this query is this.

SELECT row_number() OVER (ORDER BY MQ."Id") AS "LOS", "LevelOfServiceConfig"
 FROM "ProjectMqs" MQ
 WHERE MQ."ProjectId" = 500 AND "Id" = 1;

I need to generate a result that will be exactly the same as the result above. So this one row is split into 3 rows and its value is either less to zero or more to 999.

SELECT "Id" as "LOS", "LevelOfServiceConfig" ->> 'Danger' AS "Danger", "LevelOfServiceConfig" ->> 'TrafficJam' AS "TrafficJam"
FROM "ProjectMqs"
WHERE "ProjectId" = 500
  AND NULLIF(regexp_replace("Name", '\D','','g'), '')::numeric = 1;

I don't know what to do with this query to get the result.

CodePudding user response：

You already know how to create one row, just use UNION ALL or a similar construct to create the three rows:

SELECT ROW_NUMBER() OVER (ORDER BY v.SpeedAtLeast) AS LOS, v.*
FROM t
CROSS JOIN LATERAL (VALUES
    (999, (t.LevelOfServiceConfig->>'Danger')::int),
    ((t.LevelOfServiceConfig->>'Danger')::int, (t.LevelOfServiceConfig->>'TrafficJam')::int),
    ((t.LevelOfServiceConfig->>'TrafficJam')::int, 0)
) AS v(SpeedLessThan, SpeedAtLeast)
ORDER BY LOS